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] )
        )
    )
Palmtop's avatar
Palmtop
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity 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] ) )
)
)

  • Palmtop's avatar
    Palmtop
    Icon for Helper I rankHelper I
    3 years ago

    Hi tamerj1 , 

     

    Thanks for the reply.  I've tried it but again, it will return TotalSalesMulti if I select all 3 ID_Tasks.  

     

    In this configuration I'd like it to return 351, 856, 231 in my TotalSales column instead of 351, 765, 231.  BUT still return i.e. 562 if I only select ID_Task = 1055.  That's the part I'm struggling.  

     

    Hope that makes sense.