Forum Discussion
Conditional sums based on another measure
Hi, francoisl
I've simply modelled some data to hopefully fit your situation.
If [Sales], [Cost], and [%] are all measures, you can try the following.
M1 = Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0)) Return IF(HASONEVALUE('Table'[Date]),[Sales],SUMX(FILTER('Table',[Date]<>_blankDate),[Sales]))M2 = Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0)) Return IF(HASONEVALUE('Table'[Date]),[Cost],SUMX(FILTER('Table',[Date]<>_blankDate),[Cost]))M% = Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0)) Return IF(HASONEVALUE('Table'[Date]),[%],DIVIDE([M2],[M1]))Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Of Course.
Here's a table with data.
The total should only be totals based on the period where the cost <> 0. Meaning we want to ignore any periods that have a 0$ cost.
At this point it ignores only fiscal period P8.
Many thanks
Francois
| 2023-P8 | 2023-P9 | 2023-P10 | 2023-P10 | Total | |||||||||||
| Sales | Cost | % | Sales | Cost | % | Sales | Cost | % | Sales | Cost | % | Sales | Cost | % | |
| Dept1 | 1000 | 0 | 0.00% | 1200 | 120 | 10.00% | 500 | 75 | 15.00% | 2000 | 230 | 11.50% | 3700 | 425 | 11.49% |
| Dept2 | |||||||||||||||
| Dept3 |
Hi, francoisl
I've simply modelled some data to hopefully fit your situation.
If [Sales], [Cost], and [%] are all measures, you can try the following.
M1 =
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[Sales],SUMX(FILTER('Table',[Date]<>_blankDate),[Sales]))M2 =
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[Cost],SUMX(FILTER('Table',[Date]<>_blankDate),[Cost]))M% =
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[%],DIVIDE([M2],[M1]))
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.