Matrix View , comparison of row value with grand total

[shibabrata27]

Hi, I have AccountName in rows in matrix view, in column I have 4 quarters ( q1, q2, q3, q4 ), q4 is the latest quarter. I have grand total as well in the matrix view. Values I am showing one calculated measure i.e Margin% =( SUM(Margin) / SUM(Revenue) ) Here I have two slicer like Region and Country. Based on these slicer selection, this matrix view will get changed. Here I want to show those accounts which are having Margin% less than the grand total Margin value for latest quarter i.e q4. Kindly help me how to achieve this Dataset: Account Quarter Region Country Margin Revenue A Q1 R1 C1 11.69883234 245.6557856 A Q2 R1 C1 87.61224938 414.6794148 A Q3 R2 C2 52.31256863 282.3203253 A Q4 R1 C1 82.64066212 18.9217884 B Q1 R1 C1 85.89276056 674.3169941 B Q2 R2 C2 68.47479214 466.3047115 B Q3 R1 C1 75.32798786 10.42402663 B Q4 R1 C1 79.48266024 44.09167696 C Q1 R2 C2 71.68699798 849.7585452 C Q2 R1 C1 59.3062885 227.4537101 C Q3 R1 C1 77.48678121 245.352352 C Q4 R2 C2 24.63240763 541.8037759 D Q1 R1 C1 44.89191535 717.4897318 D Q2 R1 C1 58.03225745 616.5546888 D Q3 R2 C2 81.7754398 106.6659061 D Q4 R1 C1 11.37339745 938.7003925 Output Accounts Q1 Q2 Q3 Q4 A 4.76% 21.13% 18.53% 436.75% B 12.74% 14.68% 722.64% 180.27% C 8.44% 26.07% 31.58% 4.55% D 6.26% 9.41% 76.67% 1.21% Grand Total 8.61% 15.85% 44.50% 12.84% Here I should get accounts C, D since these are having Q4 Margin less than Grand Total Q4 (latest quarter) value

[shibabrata27]

This is really helpful, I am able to get correct result. Now let if I have one level up data i.e. UserName which will have mulptiple accounts in his name and in rows section I UserName, then Account . Now I want to show those accounts under respective users for which current quarter Margin% is less than User Level Margin %. E.g User 1 Margin % is 60 % Account1 65% Account2 45% Accouint3 34% User2 Margin% is 43% Account4 45% Account5 67% Account6 34% My final output should have User 1 Margin % is 60 % Account2 45% Accouint3 34% User2 Margin% is 43% Account6 34% Kindly help me on this.

[v-yulgu-msft]

Hi @shibabrata27 ,

Since the original question has been resolved, please kindly mark the helpful reply as an answer so that it can benefit more users. For any further question, I would suggest you post a new thread on forum so that more community members can view it and provide advice.

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @shibabrata27 ,

Add a calculated column in dataset table to always identify the latest quarter.

Rank quarter =
RANKX ( 'Dataset', 'Dataset'[Quarter],, DESC, DENSE )

Add below measure into table visual and set its value to 0.

check =
VAR Per_Account =
    CALCULATE (
        SUM ( 'Dataset'[Margin] ),
        FILTER ( 'Dataset', 'Dataset'[Rank quarter] = 1 )
    )
        / CALCULATE (
            SUM ( 'Dataset'[Revenue] ),
            FILTER ( 'Dataset', 'Dataset'[Rank quarter] = 1 )
        )
VAR All_Account =
    CALCULATE (
        SUM ( 'Dataset'[Margin] ),
        FILTER ( ALLSELECTED ( 'Dataset' ), 'Dataset'[Rank quarter] = 1 )
    )
        / CALCULATE (
            SUM ( 'Dataset'[Revenue] ),
            FILTER ( ALLSELECTED ( 'Dataset' ), 'Dataset'[Rank quarter] = 1 )
        )
RETURN
    IF ( Per_Account < All_Account, 0, 1 )

Best regards,

Yuliana Gu