Forum Discussion

Zdenek_Moravec's avatar
Zdenek_Moravec
Brass Contributor
Feb 07, 2020
Solved

Power Query Get and Transform to get totals and subtotals

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 ...
BI & Data Analysis
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 07, 2020

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 07, 2020

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.

  • Zdenek_Moravec's avatar
    Zdenek_Moravec
    Brass Contributor
    Feb 10, 2020

    SergeiBaklan 

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 10, 2020

      Zdenek_Moravec 

      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.

       

      • Zdenek_Moravec's avatar
        Zdenek_Moravec
        Brass Contributor
        Feb 11, 2020
        Hi Sergei
        All right, thank you very much.
        Zdenek
  • Zdenek_Moravec's avatar
    Zdenek_Moravec
    Brass Contributor
    Feb 07, 2020

    SergeiBaklan 

    Hi Sergei

    So easy is it, unbelievable.

    Thank you very much for your quick reply!

    Zdenek

     

Resources