Forum Discussion
Measure with different filters based on row value
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.
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 rowGrand 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())
3 Replies
- amitchandak
Super User
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 rowGrand 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())Thanks a lot! I worked as intended
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
