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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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