Forum Discussion

akhilduvvuru's avatar
akhilduvvuru
Icon for Helper IV rankHelper IV
2 years ago
Solved

MAX totals incorrect when using field parameters

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!!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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!