Forum Discussion
Power Query Get and Transform to get totals and subtotals
Hi 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.
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
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.
- Hi Sergei
All right, thank you very much.
Zdenek