SOLVED
Home

Power Query Get and Transform to get totals and subtotals

%3CLINGO-SUB%20id%3D%22lingo-sub-1157819%22%20slang%3D%22en-US%22%3EPower%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157819%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20have%20a%20CSV%20file%20with%20columns%20like%20document%20number%20and%20city.%20I%20load%20it%20to%20a%20Data%20Model%20using%20Power%20Query%20and%20I%20want%20to%20create%20a%20dashboard%20from%20the%20data%20(Office%20365).%3C%2FP%3E%3CP%3EIn%20a%20dashboard%2C%20I%20would%20like%20to%20display%20a%20key%20figure%20%22total%20number%20of%20documents%22%2C%20then%20a%20slicer%20with%20cities%20and%20a%20key%20figure%20%22Number%20of%20documents%20of%20selected%20city%22.%3C%2FP%3E%3CP%3EThe%20issue%20is%2C%20that%20one%20document%20can%20contain%20more%20cities%20and%20one%20city%20can%20appear%20in%20more%20documents.%3C%2FP%3E%3CP%3EI%20don't%20know%2C%20how%20to%20transform%20the%20table%20to%20get%20both%20key%20figures%20from%20one%20data%20source.%3C%2FP%3E%3CP%3ESee%20example%20attached.%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20a%20help.%3C%2FP%3E%3CP%3EZdenek%20Moravec%3C%2FP%3E%3CP%3ECesky%20Krumlov%2C%20Czech%20Republic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1157819%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157916%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_CK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Zdenek%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20have%20result%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20430px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169593i2C69946186294506%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20load%20by%20Power%20Query%20your%20data%20to%20data%20using%20it%20as%20connection%20only.%20In%20data%20model%20to%20add%20two%20measures%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ENumber%20of%20City%20Documents%3A%3DDISTINCTCOUNT(CSVtab%5BDocNo%5D)%0ATotal%20Number%20of%20Documents%3A%3DCALCULATE(DISTINCTCOUNT(CSVtab%5BDocNo%5D)%2CALL(CSVtab%5BCity%5D))%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EBuild%20PivotTable%20on%20data%20model%20taken%20only%20these%20two%20measures%20as%20values%20and%20add%20slicer%20to%20it.%20From%20PivotTable%20menu%20transform%20PivotTable%20to%20formulas.%20Returned%20cube%20formula%20for%20first%20measure%20name%20will%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCUBEMEMBER(%22ThisWorkbookDataModel%22%2C%22%5BMeasures%5D.%5BNumber%20of%20City%20Documents%5D%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eyou%20may%20cut%2Fpaste%20it%20into%20proper%20place%20in%20your%20dashboard.%20The%20value%20for%20this%20measure%20will%20be%20returned%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCUBEVALUE(%22ThisWorkbookDataModel%22%2CJ%2411%2CSlicer_City)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eyou%20also%20could%20move%20it%20into%20proper%20place%2C%20the%20only%20change%20reference%20in%20formula%20on%20measure%20name%20on%20proper%20one%20(here%20is%20J%2411%20to%20be%20changed%20if%20move).%3C%2FP%3E%0A%3CP%3ESame%20with%20second%20measure%20name%20and%20value.%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1158061%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1158061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3ESo%20easy%20is%20it%2C%20unbelievable.%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20quick%20reply!%3C%2FP%3E%3CP%3EZdenek%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1158680%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1158680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_CK%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1163641%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1163641%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Sergei%3C%2FP%3E%3CP%3EI%20try%20to%20understand%20the%20measures.%20I%20have%20put%20cities%20to%20a%20pivot%20table%2C%20I%20have%20added%20DocNo%20to%20values%20as%20Count%20and%20I%20have%20added%20your%20function%20Number%20of%20City%20Documents%20to%20values.%3C%2FP%3E%3CP%3EI%20don't%20understand%2C%20why%20the%20grand%20total%20of%20the%20function%20column%20is%204%2C%20if%20the%20sum%20of%20the%20single%20rows%20is%206.%3C%2FP%3E%3CP%3ESee%20attached%20image.%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3EZdenek%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1163775%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1163775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_CK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Zdenek%2C%3C%2FP%3E%0A%3CP%3ENumber%20of%20City%20Documents%20calculates%20number%20of%20unique%20documents.%20If%20we%20select%20Berlin%20and%20Stuttgart%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20437px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F170031i0230A7B108D8A331%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eeach%20of%20them%20has%20one%20unique%20document%2C%20but%20both%20together%20also%20have%20only%20one%20and%20same%20MUCK%202%20document%2C%20thus%20grand%20total%20shows%20one%20for%20these%20two%20cities.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1166644%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1166644%22%20slang%3D%22en-US%22%3EHi%20Sergei%3CBR%20%2F%3EAll%20right%2C%20thank%20you%20very%20much.%3CBR%20%2F%3EZdenek%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1166715%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Get%20and%20Transform%20to%20get%20totals%20and%20subtotals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1166715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_CK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Zdenek%2C%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20the%20update%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

7 Replies
Highlighted
Solution

@Zdenek_CK 

Hi Zdenek,

 

To have result like this

image.png

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.

Highlighted

@Sergei Baklan 

Hi Sergei

So easy is it, unbelievable.

Thank you very much for your quick reply!

Zdenek

 

Highlighted
Highlighted

@Sergei Baklan 

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

Highlighted

@Zdenek_CK 

Hi Zdenek,

Number of City Documents calculates number of unique documents. If we select Berlin and Stuttgart

image.png

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.

 

Highlighted
Hi Sergei
All right, thank you very much.
Zdenek
Highlighted

@Zdenek_CK 

Hi Zdenek,

Thank you for the update, glad to help