Forum Discussion

atul9063's avatar
atul9063
Helper III
7 years ago
Solved

Non Additive Hierarchy Slicer

Hi,

 

I want to create Non Additive Heirarchy Slicer.When user select Americas in Hierarchy slicer, then All value which is 200 should display in graph instead of Total 470.

 

 

Dataset 

 

DateMetricRegionCountryValue
2/1/2019 0:00EUAmericasAll200
2/1/2019 0:00EUAmericasCanada100
2/1/2019 0:00EUAmericasMexico50
2/1/2019 0:00EUAmericasUS120
2/1/2019 0:00EUAsiaAll500
2/1/2019 0:00EUAsiaChina400
2/1/2019 0:00EUAsiaIndia200
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi atul9063 

     

    You may create a measure like below:

    Measure = 
IF (
    HASONEVALUE ( Table3[Country] ),
    SUM ( Table3[Value] ),
    CALCULATE ( SUM ( Table3[Value] ), Table3[Country] = "ALL" )
)

    Regards,

    Cherie

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi atul9063 

     

    You may create a measure like below:

    Measure = 
IF (
    HASONEVALUE ( Table3[Country] ),
    SUM ( Table3[Value] ),
    CALCULATE ( SUM ( Table3[Value] ), Table3[Country] = "ALL" )
)

    Regards,

    Cherie

    • atul9063's avatar
      atul9063
      Helper III
      7 years ago

      Hi v-cherch-msft 

      I have added two heirarchy slicer Client and UserType.As per above solution, its working fine for ClientType but not for UserType.For UserType is adding up values.Could you please check below DAX and let me know what i m doing wrong?

       

      ClientMeasure =
      IF (
          HASONEVALUE ( r1Metrics[ClientType] ) ||  HASONEVALUE ( r1Metrics[UserType] ) ,
          SUM ( r1Metrics[Measure] ),
          CALCULATE ( SUM ( r1Metrics[Measure] ), r1Metrics[ClientType] = "ALL", r1Metrics[UserType]="All" )
      )