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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.