Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tamerlaaane
Frequent Visitor

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]

Tamerlaaane_0-1678097138736.png

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:

Tamerlaaane_1-1678097507556.png

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!

 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

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

Thanks so much, this works!

Tamerlaaane
Frequent Visitor

Anyone? 🙂

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.