Feb 07 2020 12:39 AM
Hello
I have a CSV file with columns like document number and city. I load it to a Data Model using Power Query and I want to create a dashboard from the data (Office 365).
In a dashboard, I would like to display a key figure "total number of documents", then a slicer with cities and a key figure "Number of documents of selected city".
The issue is, that one document can contain more cities and one city can appear in more documents.
I don't know, how to transform the table to get both key figures from one data source.
See example attached.
Thank you very much for a help.
Zdenek Moravec
Cesky Krumlov, Czech Republic
Feb 07 2020 01:39 AM
SolutionHi Zdenek,
To have result like this
you may load by Power Query your data to data using it as connection only. In data model to add two measures
Number of City Documents:=DISTINCTCOUNT(CSVtab[DocNo])
Total Number of Documents:=CALCULATE(DISTINCTCOUNT(CSVtab[DocNo]),ALL(CSVtab[City]))
Build PivotTable on data model taken only these two measures as values and add slicer to it. From PivotTable menu transform PivotTable to formulas. Returned cube formula for first measure name will be like
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Number of City Documents]")
you may cut/paste it into proper place in your dashboard. The value for this measure will be returned as
=CUBEVALUE("ThisWorkbookDataModel",J$11,Slicer_City)
you also could move it into proper place, the only change reference in formula on measure name on proper one (here is J$11 to be changed if move).
Same with second measure name and value.
Please check in attached file.
Feb 07 2020 02:54 AM
Feb 07 2020 07:20 AM
@Zdenek_Moravec , you are welcome
Feb 10 2020 12:44 AM
Hello Sergei
I try to understand the measures. I have put cities to a pivot table, I have added DocNo to values as Count and I have added your function Number of City Documents to values.
I don't understand, why the grand total of the function column is 4, if the sum of the single rows is 6.
See attached image.
Thank you.
Zdenek
Feb 10 2020 02:04 AM
Hi Zdenek,
Number of City Documents calculates number of unique documents. If we select Berlin and Stuttgart
each of them has one unique document, but both together also have only one and same MUCK 2 document, thus grand total shows one for these two cities.
Feb 11 2020 06:35 AM
Feb 11 2020 07:00 AM
Feb 07 2020 01:39 AM
SolutionHi Zdenek,
To have result like this
you may load by Power Query your data to data using it as connection only. In data model to add two measures
Number of City Documents:=DISTINCTCOUNT(CSVtab[DocNo])
Total Number of Documents:=CALCULATE(DISTINCTCOUNT(CSVtab[DocNo]),ALL(CSVtab[City]))
Build PivotTable on data model taken only these two measures as values and add slicer to it. From PivotTable menu transform PivotTable to formulas. Returned cube formula for first measure name will be like
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Number of City Documents]")
you may cut/paste it into proper place in your dashboard. The value for this measure will be returned as
=CUBEVALUE("ThisWorkbookDataModel",J$11,Slicer_City)
you also could move it into proper place, the only change reference in formula on measure name on proper one (here is J$11 to be changed if move).
Same with second measure name and value.
Please check in attached file.