Forum Discussion

mf2499pbi's avatar
mf2499pbi
Frequent Visitor
3 years ago
Solved

Hierarchy Not Rolling Up As expected

Hi All, 

 

I am using a switch function to sum up values in a Power BI matrix, but the hierarchy is not rolling up as I expected it to. I am using a switch function to flip between the value I want for France and the values for the rest of countries. 

 

Here is my DAX:

Test Rollup  = switch(SELECTEDVALUE('Hierarchy'[level2]), "FRANCE", sum('Table'[Amount1])/1000, sum('Table'[Amount2] )/1000)
 
The Matrix seems to be only rolling up the values for the second argument in the switch function. I would the total to be 1,602.57 as France should be zero, but it is adding the value for Amount2 instead of Amount1. Does anyone know why this is? Please see image below and let me know if there are any questions. 
 

 

DAX
SELECTEDVALUE
Switch Metric
  • v-xiaotang's avatar
    v-xiaotang
    3 years ago

    Hi mf2499pbi 

    Thanks for reaching out to us.

    please try the measures below

    Measure = SWITCH(SELECTEDVALUE('Table'[Column1]),"FRANCE",sum('Table'[amount1])/1000, sum('Table'[amount2] )/1000)
    TEST = IF(HASONEFILTER('Table'[Column1]),[Measure],SUMX('Table',[Measure]))

    then put measure [TEST] into matrix,

     

    Best Regards,

    Community Support Team _Tang

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

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi mf2499pbi 

    Thanks for reaching out to us.

    please try the measures below

    Measure = SWITCH(SELECTEDVALUE('Table'[Column1]),"FRANCE",sum('Table'[amount1])/1000, sum('Table'[amount2] )/1000)
    TEST = IF(HASONEFILTER('Table'[Column1]),[Measure],SUMX('Table',[Measure]))

    then put measure [TEST] into matrix,

     

    Best Regards,

    Community Support Team _Tang

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    mf2499pbi , Try like

    Test Rollup =

    Sumx(Values(('Hierarchy'[level2] ) , calculate(switch(SELECTEDVALUE('Hierarchy'[level2]), "FRANCE", sum('Table'[Amount1])/1000, sum('Table'[Amount2] )/1000)) ))