Measure with different filters based on row value

[orlando9427]

Hi,

I'm trying to generate a measure with total sales per division. Each division has its own way of calculate sales and they are stored in a table like this.

DIVISION	DIVISION_NAME
0	B2B
1	B2C

Originally I used a Measured Column with the function SWITCH so each case has its own filters, like this.

Measured Column = SWITCH([DIVISION], 
0, CALCULATE(SUM(SALES[TOTAL_PRICE]), FILTER(SALES, SALES[A] = 1),
1, CALCULATE(SUM(SALES[TOTAL_PRICE]), FILTER(SALES, SALES[A] <> 1 && SALES[B] <> 1)
)

So I get something like this

DIVISION	DIVISION_NAME	Measured Column
0	B2B	150000
1	B2C	48000

That worked fine until the necessity of apply time comparison via visual slicer, the problem is that the measure don't apply time filtering so I get always the total sales.

I understand that measured columns are not restricted to visuals, but if I use a Measure it gives me and error when using the SWITCH function, is there a way to do this?

Thanks a lot for your help!

PS. Sorry for my bad English.

[Anonymous]

Hi  @orlando9427 ,

I created some data:

You can use the ALLSELECT() function and the results will change with the filtered range

Here are the steps you can follow：

1. Create measure.

Measure =
SWITCH(
    TRUE(),
    MAX('Table'[DIVISION])= 0,
    SUMX(
        FILTER(ALLSELECTED(Sales),
        'Sales'[A]=1),[TOTAL_PRICE]),
    MAX('Table'[DIVISION])=1,
    SUMX(
        FILTER(ALLSELECTED('Sales'),
        'Sales'[A]<>1&&'Sales'[B]<>1),[TOTAL_PRICE]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak · Accepted solution]

@orlando9427 , Based on whatI got

For correct grand total

Measured = Sumx(values([DIVISION]) , SWITCH([DIVISION],
0, CALCULATE(SUM(SALES[TOTAL_PRICE]), FILTER(SALES, SALES[A] = 1)),
1, CALCULATE(SUM(SALES[TOTAL_PRICE]), FILTER(SALES, SALES[A] <> 1 && SALES[B] <> 1)
)) )

for GT in each row

Grand total Measured = calculate( Sumx(values([DIVISION]) , SWITCH([DIVISION],
0, CALCULATE(SUM(SALES[TOTAL_PRICE]), FILTER(SALES, SALES[A] = 1)),
1, CALCULATE(SUM(SALES[TOTAL_PRICE]), FILTER(SALES, SALES[A] <> 1 && SALES[B] <> 1)
))), allselected())

[orlando9427]

Thanks a lot! I worked as intended