Forum Discussion

aredmond127's avatar
aredmond127
Frequent Visitor
4 years ago

Calculate using multiple filters

Hi all,   I'm trying to create a measure where 'Current CDP - Total' column is summed with the following conditions: - If OOS column is blank, sum the whole column regardless of month  - If OOS c...
Need Help
speedramps's avatar
speedramps
Super User
4 years ago

Try this

 

Totalvalue =

CALCULATE(
SUM(yourtable[yourvalue]),

ALLEXPEPT(yourtable, yourtable[Region],yourtable[Sku]
) 

Totalvalue3m =

CALCULATE(
SUM(yourtable[yourvalue]),

ALLEXPEPT(yourtable, yourtable[Region],yourtable[Sku],
ISBLANK(yourtable[N3M])y

) 

 

Your answer =
IF (
ISBLANK(   SELECT(yourtable[OOS])  ),
Totalvalue3M,
Totalvalue) 

 

Please cleick thumbs up and accept as solution. Thanks ðŸ˜€

  • aredmond127's avatar
    aredmond127
    Frequent Visitor
    4 years ago

    Hi, 

     

    thanks for this however it is not quite working - rather than removing current CDP values for only N3M, it is not populating it at all. Please see screenshot below.

     

    Note I did not use the all except part - apologies I didn't make it clear in the original post. I want users to be able to adjust granularity for all report measure i.e. region, brand, category, sub category etc. so I believe I don't need this part?