Forum Discussion

orlando9427's avatar
orlando9427
Frequent Visitor
2 years ago
Solved

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 B2...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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's avatar
orlando9427
Frequent Visitor
2 years ago

Thanks a lot! I worked as intended