Power Query Get and Transform to get totals and subtotals

Zdenek_Moravec 

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.