MAX totals incorrect when using field parameters

[akhilduvvuru]

Hi Team,

I have a table "Table1" with multiple dimensions (Country, State, District, Product, Sub Product etc.,). I have created a field parameter with all these dimensions so that I can use this field parameter basically to select the dim by my choice, instead of adding all of the dims in the table.

I also have target amount field. My requirement is to show the max target value by (selected dimension from field parameter list). Values are showing correct. However, the totals are showing wrong.

I tried this logic

Measure1 = CALCULATE(MAX(Table1[Target]))

Measure2 = SUMX(VALUES(Table1[Country]), MAXX(VALUES(Table1[Target]),[Measure1]))

Measure3= SUMX(GROUPBY(Table1,('Table1'[Country]) ),[Measure1])

 Both Measure2 and Measure3 are showing right totals only for the dimension which I explicitly mentioned in the logic (Country). However, if I change the dimension from Country to State or District etc., the totals are not showing correct.

Not sure on how to update the logics to work dynamically based on the selected dimention (field Parameter).

Can someone please help on this?

Thanks!!

[v-jingzhan-msft · Accepted solution]

Hi @akhilduvvuru 

Assume that your field parameter table is like below, 

You can create a new measure similar to below measure. Use a switch function to calculate the totals based on different dimension columns individually according to the selected dimension's order number. 

New Measure = 
    SWITCH(
        SELECTEDVALUE('Dimension'[Dimension Order]),
            0, SUMX(GROUPBY('Table1',Table1[Country]),[Measure1]),
            1, SUMX(GROUPBY('Table1',Table1[District]),[Measure1]),
            2, SUMX(GROUPBY('Table1',Table1[State]),[Measure1])
    )

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[akhilduvvuru]

Thanks @v-jingzhan-msft - Really works!