topic Re: SUMX Different Column Depending On IF Condition in DAX Commands and Tips

SUMX Different Column Depending On IF Condition

Palmtop — Fri, 05 May 2023 14:59:56 GMT

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!

Re: SUMX Different Column Depending On IF Condition

johnt75 — Fri, 05 May 2023 15:46:35 GMT

Try changing the duplicates measure to

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

Re: SUMX Different Column Depending On IF Condition

tamerj1 — Fri, 05 May 2023 16:00:57 GMT

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

Re: SUMX Different Column Depending On IF Condition

Palmtop — Fri, 05 May 2023 16:15:12 GMT

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!

Re: SUMX Different Column Depending On IF Condition

Palmtop — Fri, 05 May 2023 16:19:59 GMT

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!

Re: SUMX Different Column Depending On IF Condition

tamerj1 — Fri, 05 May 2023 16:27:12 GMT

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

Re: SUMX Different Column Depending On IF Condition

Palmtop — Tue, 09 May 2023 07:49:34 GMT

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.

Re: SUMX Different Column Depending On IF Condition

tamerj1 — Tue, 09 May 2023 08:28:09 GMT

Palmtop
Any chance you can share your sample file?

Re: SUMX Different Column Depending On IF Condition

Palmtop — Tue, 09 May 2023 09:12:07 GMT

Hi tamerj1 ,

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

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

Thanks!

Re: SUMX Different Column Depending On IF Condition

tamerj1 — Tue, 09 May 2023 12:01:30 GMT

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

Re: SUMX Different Column Depending On IF Condition

Palmtop — Tue, 09 May 2023 12:35:04 GMT

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!