Forum Discussion

atul9063's avatar
atul9063
Icon for Helper III rankHelper 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
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Helper III rankHelper 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" )
      )