Forum Discussion

Palmtop's avatar
Palmtop
Icon for Helper I rankHelper 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 Tot...
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] )
        )
    )
johnt75's avatar
johnt75
Icon for Super User rankSuper 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
Icon for Helper I rankHelper 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!