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.
can you give an example of what you are expecting?
then also provide some sample data with desired outcome. This will help to ensure your problem is fully understood.
you can either copy and paste text data with an example what you are expecting.
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 |
is this how the table looks in power bi? are the periods column names?
Yes Periods are column names and from a calendar tables.
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.