Forum Discussion

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

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...
DAX
Need Help
talespin's avatar
talespin
Solution Sage
2 years ago

You're welcome.

    • talespin's avatar
      talespin
      Solution Sage
      2 years ago

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