Forum Discussion

Palmtop's avatar
Palmtop
Helper I
3 years ago
Solved

SUMX Different Column Depending On IF Condition

Hello All,

 

I am struggling with a measure that is trying to accomplish the following:

 

I have a set of ID_Tasks attributed to ID_Project and I want to find the TotalSales by summing either TotalSalesSingle or TotalSalesMulti depending on if my dynamic selection (slicer on ID_Task) contains duplicate ID_Project or not.  

 

 

I have a first measure that checks if my selection contains duplicates or not, and counts them.  

 

 

Duplicates = CALCULATE(COUNT(SalesProfiles[ID_Project]), ALLSELECTED(SalesProfiles), VALUES(SalesProfiles[ID_Project]))

 

 

Basically, I am looking for the following results:

1.  If I select 2 ID_Task that are from different ID_Project, I want to sum the TotalSalesSingle value for each. 

2.  If I select 2 ID_Task that are from a shared ID_Project, I want to sum the TotalSalesMulti value for each. 

3.  If I select all 3 ID_Task , 2 of which share a ID_Project, and 1 does not; I want to sum the TotalSalesMulti for the 2, and TotalSalesSingle for the 1.  

aka it will sum values from either TotalSalesSingle or Multi depending on Duplicates = 1 or != 1, evaluated for each row. 

 

Scenarios:

Single Selection:  ID_Project is unique value, returns TotalSalesSingle for Total Sales

Multi Selection: ID_Project is non-unique value, returns TotalSalesMulti for TotalSales

Here you can see that my measure TotalSales doesnt work.  I'd like it to return 231 & 351 (TotalSalesMulti column)

Multi Selection: All 3 selected.  

Doesn't work either. In this case I'd like it to return TotalSalesMulti (231 & 351) for ID_Project = 38515, and TotalSalesSingle (856) for ID_Project = 29677.  

 

This is my current measure for TotalSales which is far from working.  

 

Total Sales = 
SUMX ( ProjetList, 
    IF(
         [Duplicates] = 1, 
    SUMX ( SalesProfiles , SalesProfiles[TotalSalesSingle] )
    , 
    SUMX ( SalesProfiles , SalesProfiles[TotalSalesMulti] )
    )
)

 

 

I've included the files below. 

 

https://1drv.ms/f/s!AndVpJp2VQectSrHRNEZnOIC2Kqj?e=CA0QS6

 

Please let me know what you think, and if I wasn't clear about anything.  Thank you!

 

 

DAX SUMX
Help Requested
  • tamerj1's avatar
    tamerj1
    3 years ago

    Palmtop 
    Sorry fo the late response. The problem is coming from the ALLSELECTED in the Duplicate measure. This has to be done manually.

    Total Sales = 
VAR SelectedTable = ALLSELECTED ( SalesProfiles )
RETURN
    SUMX ( 
        SUMMARIZE ( SalesProfiles, ProjetList[ID_Project], ProjetList[ID_Task] ),
        IF (
            CALCULATE ( 
                COUNT ( SalesProfiles[ID_Project] ), 
                SelectedTable, 
                VALUES ( SalesProfiles[ID_Project] ) 
            ) = 1,
            SUM ( SalesProfiles[TotalSalesSingle] ), 
            SUM ( SalesProfiles[TotalSalesMulti] )
        )
    )

10 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try changing the duplicates measure to

    Duplicates = CALCULATE(
            COUNT( SalesProfiles[ID_Project] ),
            ALLEXCEPT( SalesProfiles, SalesProfiles[ID_Project])
        )
    • Palmtop's avatar
      Palmtop
      Helper I
      3 years ago

      Hi johnt75 , 

       

      Thanks for the suggestion.  I gave it a try, but I need it change depending on the dynamic selection.  So in this case, with only ID_Task = 7799 selected, it should give [Duplicates] = 1.  It would only return = 2 if both ID_Task = 7799 & 1055 were selected.  

       

      Thanks!

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Palmtop 

    please try

    Total Sales =
    VAR SelectedTasks =
    ALLSELECTED ( ProjetList[ID_Task] )
    RETURN
    SUMX (
    VALUES ( ProjetList[ID_Project] ),
    IF (
    COUNTROWS (
    CALCULATETABLE (
    VALUES ( ProjetList[ID_Task] ),
    ALL ( ProjetList ),
    VALUES ( ProjetList[ID_Project] ),
    SelectedTasks
    )
    ) = 1,
    CALCULATE ( SUM ( SalesProfiles[TotalSalesSingle] ) ),
    CALCULATE ( SUM ( SalesProfiles[TotalSalesMulti] ) )
    )
    )

    • Palmtop's avatar
      Palmtop
      Helper I
      3 years ago

      Hi tamerj1 , 

       

      Thanks for the quick reply,  I gave it a try but it gave me this - 

      The TotalSales takes the correct values from column "TotalSalesSingle" (856 and 698) but the Total returns 996 instead of 1554.  Any thoughts as to why?

       

      Thanks!

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Palmtop 

        please try

        Total Sales =
        VAR SelectedTasks =
        ALLSELECTED ( ProjetList[ID_Task] )
        VAR AllProjectList =
        ALL ( ProjetList )
        RETURN
        SUMX (
        VALUES ( ProjetList[ID_Project] ),
        IF (
        COUNTROWS (
        FILTER (
        AllProjectList,
        ProjetList[ID_Project] = ProjetList[ID_Project]
        && ProjetList[ID_Task] IN SelectedTasks
        )
        ) = 1,
        CALCULATE ( SUM ( SalesProfiles[TotalSalesSingle] ) ),
        CALCULATE ( SUM ( SalesProfiles[TotalSalesMulti] ) )
        )
        )