Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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] )
)
)
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] ) )
)
)
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!
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] ) )
)
)
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.
Hi @tamerj1 ,
Of course, please let me know if the link doesn't work.
https://1drv.ms/f/s!AndVpJp2VQectSrHRNEZnOIC2Kqj?e=CA0QS6
Thanks!
@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] )
)
)
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!
Try changing the duplicates measure to
Duplicates = CALCULATE(
COUNT( SalesProfiles[ID_Project] ),
ALLEXCEPT( SalesProfiles, SalesProfiles[ID_Project])
)
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |