Forum Discussion
Request for help with DAX measure
Hi everyone,
I am having issues calculating a SUMX with a filter > 0 on a measure that is also being affected by period selection. Data model is a nice star schema with a Fact table where all the measures are coming from. I have a relative Period/Date slicer (coming from my Date table / Calendar Dimension) and three measures: a (running) Balance, which calculates the balance per day. Then we have an Allowed Balance (which is calculated dynamically based on the Period selection in my slicer), which has the following code:
Average allowed balance = CALCULATE([Allowed balance], ALLSELECTED(dim_date))
I use ALLSELECTED to get a desired average per period selection, so the number is not affected by the row context of the Date (days) in the visual. But [Allowed balance] is affected by the no. of days that are selected): Allowed balance = [IncomingItems]*[Norm]/[No. of days]
Then I have another measure which has this code:
Daily Balance Difference (DBD) = [Balance (at the end of the period)]-[Average allowed balance]
All well so far, it all works. But now I need to get a SUM of the DBD of all numbers higher then 0 (where i want to use SUMX in combination with FILTER, iterating over the Date table). When I apply the filter on the visual, the Average allowed balance recalculates, leaving me with the wrong values:
The main problem for me is that the measures need to work with the Periode/Date slicer, so i cannot use ALL(), but I am not sure how to proceed here. I just want the 'slice' the results without changing the values on Average allowed balance, but how?
(Sorry, no pbix example, it contains customer data).
Thanks in advance for the suggestions!
- Anonymous3 years ago
Hi Tamerlaaane ,
Please try this measure.
Measure = SUMX ( FILTER ( SUMMARIZE ( 'dim_date', 'dim_date'[Date], "DBD", [Balance (at the end of the period)] - [Average allowed balance] ), [DBD] > 0 ), [DBD] )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
3 Replies
- AnonymousNot applicable
Hi Tamerlaaane ,
Please try this measure.
Measure = SUMX ( FILTER ( SUMMARIZE ( 'dim_date', 'dim_date'[Date], "DBD", [Balance (at the end of the period)] - [Average allowed balance] ), [DBD] > 0 ), [DBD] )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
- TamerlaaaneFrequent Visitor
Thanks so much, this works!
- TamerlaaaneFrequent Visitor
Anyone? 🙂