How to show Grand Total of a specific Row in Matrix

[sumitnegi1266]

Hi Experts,

My data looks like below:

What I need to achieve -> If the number of sets in data are more than 1, then I need to add one more row at the bottom of matrix for Weighted Average of Set. If there is only 1 set, then I don't want to show any row for Weighted Average of Set.

Currently, I am able to show Weighted average for every set, but I need to show it once at the bottom of matrix.

Weighted Average of Set = MIN( Weight(Set) ) * AVERAGE ( 'Table'[Amount] )

As shown below the Weighted Average of Set should be total of Weighted average of both sets i.e. 122 and show once at the bottom.

Also, I have attached the PBIX file for above at below one drive location:

Power BI File Share

Any help is really appreaciated, many thanks in advance!

[Ahmedx]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26Rizd1m2YfFgWo7GH1?e=ow16GU

[Anonymous]

Hi

[sumitnegi1266]

Hi @Anonymous 

[Anonymous · Accepted solution]

Hi @sumitnegi1266 

You can create the following solution.

1.Create a calcaulated table

Table 2 = var a=SUMMARIZE(FILTER(DimCompany,[Company]<>"Weighted Average of Set"),[Set],[Rank],DimCompany[Company])
var b=ADDCOLUMNS({MAXX(ALLSELECTED(DimCompany),[Set])+1},"Rank",0,"Company","Weighted Average of Set")
return UNION(a,b)

2.Then create a measure

Measure =
VAR a =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( DimCompany ), [Company] = "Weighted Average of Set" ),
        [Set],
        "Sales", [Sales]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table 2'[Set] ) <> MAXX ( ALL ( 'Table 2' ), [Set] ),
        CALCULATE (
            [Sales],
            FILTER (
                DimCompany,
                [Set]
                    IN VALUES ( 'Table 2'[Set] )
                        && [Rank]
                            IN VALUES ( 'Table 2'[Rank] )
                                && [Company] IN VALUES ( 'Table 2'[Company] )
            )
        ),
        SUMX ( a, [Sales] )
    )

Then put the field of new table and measure to the matrix visual

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.