Create subtotal for only required hierarchy rows

Question

Hi All,&nbsp;I have attached my report below .&nbsp;Hierarchy columns used above are&nbsp; 1-&gt;2-&gt;3-&gt;4-&gt;5&nbsp;I have enabled subtoal in matrix report .So the subtotal are coming for all the row header separately&nbsp;&nbsp;But for me i want to show ony subtotal for column 3 and grand total in final&nbsp; . Is it possible in power BI ?&nbsp;Could you please anyone help on this ?&nbsp;

anonymous · Answer

stevedep&nbsp; Could you help on this pleas&nbsp;

amitchandak · Answer

Anonymous , refer if this can help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

v-yingjl · Answer

Hi&nbsp;Anonymous&nbsp;,I'm afraid not. Row total is its own attribute in Matrix, we can turn on / off globally in this visual, not only show part of rowtotals.What we can do is to use isinscope() to show part of them as blank if we need like this:Total =
IF (
    ISINSCOPE ( 'Table'[Group1] ),
    SUM ( 'Table'[Value] ),
    IF (
        ISINSCOPE ( 'Table'[Category] ),
        IF (
            SELECTEDVALUE ( 'Table'[Category] ) = "A",
            SUM ( 'Table'[Value] ),
            BLANK ()
        ),
        SUM ( 'Table'[Value] )
    )
)&nbsp;Best Regards,Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

v-yingjl&nbsp;, Thanks for your input , I dont have Blank issue .If you see my attached report format .&nbsp;&nbsp;column 3 need to show subtotal in all the split&nbsp; and finally column1 will be also enabled to show grand&nbsp; total.&nbsp;Ex report like below&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; year2017( Sales)&nbsp;X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;x2&nbsp; &nbsp; &nbsp; x3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Z2&nbsp; &nbsp; &nbsp; &nbsp;Z3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 400b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;d3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; d4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 400total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;800&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Create subtotal for only required hierarchy rows

4 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Customize subtotal in matrix

Date hierarchy

Slicer requirement

Subtotal calculation with hierarchy in matrix chart