Request for help with DAX measure

[Tamerlaaane]

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!

[v-cgao-msft · Accepted solution]

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 Team

If 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

[Tamerlaaane]

Thanks so much, this works!

[Tamerlaaane]

Anyone? 🙂