SUMX Different Column Depending On IF Condition

[Palmtop]

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!

[tamerj1]

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]

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]

@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]

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.  

[tamerj1]

@Palmtop 
Any chance you can share your sample file?

[Palmtop]

Hi @tamerj1 , 

Of course, please let me know if the link doesn't work. 

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

Thanks!

[tamerj1 · Accepted solution]

@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]

Hi @tamerj1 , 

Not at all!! Happy to say that it works just like needed and I'm learning a lot along the way.  Thanks so much for your patience and help! 

[johnt75]

Try changing the duplicates measure to

Duplicates = CALCULATE(
            COUNT( SalesProfiles[ID_Project] ),
            ALLEXCEPT( SalesProfiles, SalesProfiles[ID_Project])
        )

[Palmtop]

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!