Forum Discussion

123abc's avatar
123abc
Community Champion
2 years ago
Solved

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
            )
        )
    )
 
 

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

 

 

Issue briefing 1Issue briefing 2

 

Issue briefing 3

@ talespin 

Dax Help
Need Help
  • maddy15's avatar
    maddy15
    2 years ago

    123abc 

    Can you please try below DAX using Summarize. I have used AddColumns,Summarize DAX  functions to create a virtual table and then done the calculation on 2 columns 'TotalSum' & 'TotalQualified' using SUMX, which should give the required total.

     

    Sell Out Units Jan 24 =
    VAR summarizedTable = ADDCOLUMNS(SUMMARIZE('Customer Master','Customer Master'[ACCOUNT_NUMBER],'Customer Master'[PARTY_NAME],'Customer Master'[BRANCH]),"Total Sum",[CM MTD Sales],"Total Qualified",[Qualified Units Nov 23 Onwards])
    RETURN
    SUMX(summarizedTable,
    IF(
            [Total Sum] = [Total Qualified],
            [Total Sum],
                IF(
                    [Total Sum] > [Total Qualified],
                    [Total Qualified],
                    IF(
                        [Total Qualified] > [Total Sum],
                        [Total Sum],
                        0
                    )
                 )
        )
    )
    I filtered data for few account number for a particular city. Total shows correct:

    Please try at your end.

     

    Thanks,

    Maddy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ThxAlot's avatar
    ThxAlot
    2 years ago
    Sell Out Units Jan 24 =
SUMX(
    VALUES( 'Customer Master'[ACCOUNT_NUMBER] ),
    MIN( [CM MTD Sales], [Qualified Units Nov 23 Onwards] )
)
  • 123abc's avatar
    123abc
    2 years ago

    if both measures ([CM MTD Sales] & [Qualifited Untis Nov 23 Onwards] are equal then ? 

    Sell Out Units Jan 24 =
SUMX(
    VALUES( 'Customer Master'[ACCOUNT_NUMBER] ),
    MIN( [CM MTD Sales], [Qualified Units Nov 23 Onwards] )
)

     plz guide .

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi 123abc ,

     

    Instead of the measure [Sell Out Units Jan 24] we can create a new measure [New Sell out] to be placed on the visual object.

     

    Sell Out Units Jan 24 = 
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
            )
        )
    )
    New Sell out = SUMX(
    VALUES( 'Customer Master'[ACCOUNT_NUMBER] ),[Sell Out Units Jan 24])

     

    Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

7 Replies

  • maddy15's avatar
    maddy15
    Regular Visitor
    2 years ago

    123abc 

    Can you please try below DAX using Summarize. I have used AddColumns,Summarize DAX  functions to create a virtual table and then done the calculation on 2 columns 'TotalSum' & 'TotalQualified' using SUMX, which should give the required total.

     

    Sell Out Units Jan 24 =
    VAR summarizedTable = ADDCOLUMNS(SUMMARIZE('Customer Master','Customer Master'[ACCOUNT_NUMBER],'Customer Master'[PARTY_NAME],'Customer Master'[BRANCH]),"Total Sum",[CM MTD Sales],"Total Qualified",[Qualified Units Nov 23 Onwards])
    RETURN
    SUMX(summarizedTable,
    IF(
            [Total Sum] = [Total Qualified],
            [Total Sum],
                IF(
                    [Total Sum] > [Total Qualified],
                    [Total Qualified],
                    IF(
                        [Total Qualified] > [Total Sum],
                        [Total Sum],
                        0
                    )
                 )
        )
    )
    I filtered data for few account number for a particular city. Total shows correct:

    Please try at your end.

     

    Thanks,

    Maddy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • 123abc's avatar
      123abc
      Community Champion
      2 years ago

      Thank you so much .. let me try it plz. i am out of city therefor replay you late.

    • 123abc's avatar
      123abc
      Community Champion
      2 years ago

      Excellent its work... great.

  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago
    Sell Out Units Jan 24 =
SUMX(
    VALUES( 'Customer Master'[ACCOUNT_NUMBER] ),
    MIN( [CM MTD Sales], [Qualified Units Nov 23 Onwards] )
)
    • 123abc's avatar
      123abc
      Community Champion
      2 years ago

      if both measures ([CM MTD Sales] & [Qualifited Untis Nov 23 Onwards] are equal then ? 

      Sell Out Units Jan 24 =
SUMX(
    VALUES( 'Customer Master'[ACCOUNT_NUMBER] ),
    MIN( [CM MTD Sales], [Qualified Units Nov 23 Onwards] )
)

       plz guide .

      • 123abc's avatar
        123abc
        Community Champion
        2 years ago

        its not working /// sorry.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 123abc ,

     

    Instead of the measure [Sell Out Units Jan 24] we can create a new measure [New Sell out] to be placed on the visual object.

     

    Sell Out Units Jan 24 = 
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
            )
        )
    )
    New Sell out = SUMX(
    VALUES( 'Customer Master'[ACCOUNT_NUMBER] ),[Sell Out Units Jan 24])

     

    Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.