Forum Discussion

Lily36876's avatar
Lily36876
Helper II
3 years ago

Subtotal every row title

Hi 

Can power BI subtotal every row total like this follow pic format?

ex. I have A shop every weeks total , as well as B shop

And I want to get every weeks total Amt below matrix

Because now I can only show all total below

 

Is it possible?

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    switch the position of "Type" and "Weeks" in the row well of your matrix visual.

  • johnyip's avatar
    johnyip
    Solution Sage
    3 years ago

    Lily36876 , assuming you are using the following data (name of the table is 'Data'):

     

     

     

    Please try the following:

     

    1. Create the tables "Type" and "Week" using the following DAX:

     

     

    Type = ADDCOLUMNS(SELECTCOLUMNS(UNION({"Total"},VALUES(Data[Type])),"Type",[Value]),"Order",SWITCH([Type],"A",1,"B",2,3))
----------------------------------------------------------------------------
Week = VALUES(Data[Week])

     

     

     

    2. Modify the relationships as follows:

     

    3. Sort 'Type'[Type] by 'Type'[Order]

     

    4. Create the measure [Amt] as follows:

     

     

    Amt = 
VAR CurrentWeek = MAX(Week[Week])
VAR VirtualTable = SUMMARIZE(ALLSELECTED(Data),
                             [Week],
                             "Amount",SUM(Data[Amount])
)
RETURN 
SWITCH(MAX('Type'[Type]),
             "Total",
             CALCULATE(SUMX(FILTER(VirtualTable,[Week]=CurrentWeek),[Amount]),ALLEXCEPT(Data,'Data'[Type])),
             SUM(Data[Amount]))

     

     

     

    5. Put 'Type'[Type], 'Week'[Week] and [Amt] in a table

     

    6. Finally, switch off the totals

     

    And that's it. Enjoy.

     

    A similar question of yours has been raised by someone else earlier, please feel free to look at it as well for your reference.

     

    Solved: Setting up a matrix or table - Microsoft Power BI Community