Measure to ignore certain filter/slicer, but also filter to a specific value of ignored filter

[Anonymous]

Hi All,

I am trying to create a 'line and stacked column' chart that shows:

• x axis of "Time"
• column y axis of "Sales Volume" that shows selected "Product Group" from the slicer below
• I have a slicer to select the "Product Group" (Appetizer, Entree, Drink, Dessert)
• stacked column of "Product Name"
• line y axis of "Drink upsell%" (calculated by taking "Drink upsell" and divided by total sales volume of selected "Product Group")

I got everything to work except the line y-axis.

My "Drink upsell" and "Drink upsell%" measures' formulae are:

Drink upsell = CALCULATE(sum(Data[Sales Volume]),FILTER(ALL[Data[Product Group]),Data[Product Group]="Drink"))

Drink upsell% = divide(drink upsell,sum(Data[sales volume]),0)

My measure is not calculated if I select something from "Product Group" slicer that is not "Drink", since the page will already filter out "Drink" from data.

Is there a way to do this, without creating another reference of my "Data" table? (not ideal, because it's a huge database)

Thanks!

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

I am afraid that you couldn't do it.

However, why don't you edit the measure like the following DAX:

Drink upsell =
CALCULATE ( SUM ( Data[Sales Volume] ), ALLSELECTED ( Data[Product Group] ) )