Non Additive Hierarchy Slicer

[atul9063]

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 

Date	Metric	Region	Country	Value
2/1/2019 0:00	EU	Americas	All	200
2/1/2019 0:00	EU	Americas	Canada	100
2/1/2019 0:00	EU	Americas	Mexico	50
2/1/2019 0:00	EU	Americas	US	120
2/1/2019 0:00	EU	Asia	All	500
2/1/2019 0:00	EU	Asia	China	400
2/1/2019 0:00	EU	Asia	India	200

[v-cherch-msft · Accepted solution]

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]

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" )

)