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
123abc
Community Champion
Community Champion

Required Dax Correction / Help

Hi Experts,

 

I have to measuere one is current month month to date sales "[CM MTD Sales]"

2nd is Qualified Units "[Qualified Units]"

about two measure related to two differnect tables like sales and qualificat units for incentives. belwo DAX measure works fine in matrix on row levle or party level but its net total in not corrected, in net total the dax again apply formula conditions and return less value from both Variable although i wants to sum up the data which is created by row level. plz guide how to solve the issue.

Pic is attached:

 

Applied measure works correctly on row or party lelve in martix but net total is not corrected:

ConditionalSumMeasure =
VAR TotalSum = [CM MTD Sales]
VAR TotalQualified = [Qualified Units]
RETURN
    IF(
        TotalSum = TotalQualified,
        TotalSum,
        IF(
            TotalSum > TotalQualified,
            TotalQualified,
            IF(
                TotalQualified > TotalSum,
                TotalSum,
                0
            )
        )
    )
 
Pis of issue.jpg
5 REPLIES 5
talespin
Solution Sage
Solution Sage

You're welcome.

123abc
Community Champion
Community Champion

Hi talespin !

I cant work out with your suggstions and method plz guide me with sample pbix file data.

Here is sample data please guide me, help me how to solve this issue and make net total with SUMX or with any other method. 

 

PBIX File and Supporting Data:

https://drive.google.com/file/d/1VlDRSNO5uNnfH8mZxcLXT_W4QE1QEat-/view?usp=sharing

Explanation 1Explanation 1Explanation 2Explanation 2

hi @123abc 

 

Please use this measure. Exported to csv with original measure and sum is 1455. Please note that this measure will always calculate at this level Account Number, Party Name and Branch. If you happen to include another dimension column to visual, you will have to adjust SUMMARIZE.

 

This is how this measure is working.

Creating a table using summarize with Account Number, Party Name and Branch. Since SUMX is an iterator, it will iterate each row of result of SUMMARIZE.

Each combiation of Account Number, Party Name and Branch will act as a filter for your measures 

[CM MTD Sales] and [Qualified Units Nov 23 Onwards].

 

Sell Out Units Jan 24 =
SUMX(
    SUMMARIZE('Customer Master', 'Customer Master'[ACCOUNT_NUMBER], 'Customer Master'[PARTY_NAME], 'Customer Master'[BRANCH]),
    VAR TotalSum = [CM MTD Sales]
    VAR TotalQualified = [Qualified Units Nov 23 Onwards]
    RETURN
    IF(
        TotalSum = TotalQualified,
        TotalSum,
        IF(
            TotalSum > TotalQualified,
            TotalQualified,
            IF(
                TotalQualified > TotalSum,
                TotalSum,
                0
            )
        )
    )
)
 

talespin_0-1706801181302.png

talespin_1-1706801624184.png

 

talespin
Solution Sage
Solution Sage

Hi @123abc ,

 

This is how your measure is currently working.

At row level there is a filter coming from visual, but at total level there is no filter from visual.

So TotalSum = 2986 and TotalQualified = 4277, that is why you are getting total as TotalSum.

IF(
                TotalQualified > TotalSum,
                TotalSum,
                0
            )

 

Use SUMX to fix this. and call your measures inside SUMX and pass the filters manually(though filters are propagated automatically during context transition).

 

Please refre to this article.
 
Update : 
 
Something like this, Since SUMX is an iterator. It will iterate over all products and return value based on your condition. Also your measure invokes context transition and will calculate for each product.
Modify Summarize to modify the filters(Example I see Party and Branch as filters in screenshot).
 
SUMX(
    SUMMARIZE(DimProduct, DimProduct[EnglishProductName]),
    VAR TotalSum = [MCustomQuantity]
    VAR TotalQualified = [MQuantity]
    RETURN
    IF(
        TotalSum = TotalQualified,
        TotalSum,
        IF(
            TotalSum > TotalQualified,
            TotalQualified,
            IF(
                TotalQualified > TotalSum,
                TotalSum,
                0
            )
        )
    )
)
 
talespin_1-1706587742035.png

 

If still any issue, please share pbix file with any sensitive data removed.

 

123abc
Community Champion
Community Champion

Thank You very much for your kind replay, if i have any issue regarding this then i will provide you sample data and further explanations.

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.