The following DAX measure nearly works but does not return the % Values for the red highlighted section as shown in the image. I cannot see my error in the measure below
Previously Level 3 was not working now Level 2 is not returning back the expected result. All the slicer and filter are in the same table.
Packs Market Share =
VAR _PacksCash = [Packs]
VAR _All_PacksCash_Product =
CALCULATE([Packs], removefilters(dev_hpa_sales_2[hpa_national_desc],dev_hpa_sales_2[level_1_element_desc],dev_hpa_sales_2[level_2_element_desc],dev_hpa_sales_2[level_3_element_desc]))
VAR _All_PacksCash_Market_1 =
CALCULATE([Packs], removefilters(dev_hpa_sales_2[level_1_element_desc],dev_hpa_sales_2[level_2_element_desc],dev_hpa_sales_2[level_3_element_desc]))
VAR _All_PacksCash_Market_2 =
CALCULATE([Packs], removefilters(dev_hpa_sales_2[level_2_element_desc],dev_hpa_sales_2[level_3_element_desc]))
VAR _All_PacksCash_Market_3 =
CALCULATE([Packs], removefilters(dev_hpa_sales_2[level_3_element_desc]))
VAR _Pct_of_Product =
DIVIDE(_PacksCash,_All_PacksCash_Product,0)
VAR _Pct_of_Market_1 =
DIVIDE(_PacksCash,_All_PacksCash_Market_1,0)
VAR _Pct_of_Market_2 =
DIVIDE(_PacksCash,_All_PacksCash_Market_2,0)
VAR _Pct_of_Market_3 =
DIVIDE(_PacksCash,_All_PacksCash_Market_3,0)
VAR _Result =
SWITCH(TRUE(),
ISINSCOPE( dev_hpa_sales_2[level_2_element_desc]), _Pct_of_Market_3,
ISINSCOPE( dev_hpa_sales_2[level_2_element_desc]), _Pct_of_Market_2,
ISINSCOPE( dev_hpa_sales_2[level_2_element_desc]), _Pct_of_Market_1,
ISINSCOPE( dev_hpa_sales_2[dataset_desc]), _Pct_of_Product,
_Pct_of_Product )
Return
_Result