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 trying to calculate sum of cases based on source and destination for each period.
the data looks like as below
Year | Period | Platform | Scenario Name | Source | Destination | Case |
2020 | p6 | Pretzel | Pretzel A | Dallas | Houston | 6 |
2020 | p6 | TC | TC | Dallas | Houston | 8 |
2020 | P6 | NUTS | NUTS | Dallas | Houston | 9 |
2020 | P6 | Frito | Frito | Austin | Miami | 11 |
2020 | P5 | PC | PC | Dallas | Houston | 7 |
2020 | P5 | DIPS | DIPS | Dallas | Houston | 10 |
2020 | p5 | Pretzel | Pretzel A | Dallas | Houston | 11 |
2020 | p6 | Pretzel | Pretzel A | Aberdeen | Brooklyn | 7 |
When i choose Pretzel A from Scenario Name column,
the expected output looks like as below
Year | Period | Platform | Scenario Name | Source | Destination | Case |
2020 | P6 | Pretzel | Pretzel A | Dallas | Houston | 6 + (8 + 9) |
2020 | P6 | TC | TC | Dallas | Houston | 8 + (6+9) |
Here...
i need to get sum of all cases grouped by Source - destination & Year-Period combination where
for one scenario name(in this case, Pretzel A) which are having Source and destination similar to other Scenario Names (like Dallas and Houston)
I am having difficulty to put it in words, so you can imagine, that, how i can make it work in powerbi using DAX.
I need some suggestions whether it is possible using DAX, if it is, can anyone please guide.
Thanks,
Mohan V.
Hi, @Anonymous
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a calculated column or a measure as below.
Calculated column:
Grouped Sum Column =
var _yearperiod = [Year-Period]
var _source = [Source]
var _destination = [Destination]
return
CALCULATE(
SUM('Table'[Case]),
FILTER(
ALL('Table'),
'Table'[Year-Period]=_yearperiod&&
'Table'[Source]=_source&&
'Table'[Destination]=_destination&&
NOT(CONTAINSSTRINGEXACT('Table'[Scenario Name],"- THIN REG"))
)
)
Measure:
Grouped Sum measure =
var _yearperiod = SELECTEDVALUE('Table'[Year-Period])
var _source = SELECTEDVALUE('Table'[Source])
var _destination = SELECTEDVALUE('Table'[Destination])
return
CALCULATE(
SUM('Table'[Case]),
FILTER(
ALL('Table'),
'Table'[Year-Period]=_yearperiod&&
'Table'[Source]=_source&&
'Table'[Destination]=_destination&&
NOT(CONTAINSSTRINGEXACT('Table'[Scenario Name],"- THIN REG"))
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft Thanks for your help.
Much appriciated.
I did some modifications based on your code as you gave a hardcoded contained string.
What i did is, i added Index column in table, and based on that i calculated Platform Count using below DAX
PlatformCount =
CALCULATE (
COUNT ( MasterTable[Platform] ),
FILTER (
ALLEXCEPT('MasterTable',MasterTable[Year-Period],MasterTable[Source-Dest]),
[Index] <= EARLIER ( 'MasterTable'[Index] )
&& [Platform] = EARLIER ( 'MasterTable'[Platform])
)
)
Now instead of CONTAINSTRING function i changed the code to below dax.
Grouped Sum Column =
var _yearperiod = [Year-Period]
var _source = [Source]
var _destination = [Destination]
Var _Platform = [Platform]
VAR _Scenario = MasterTable[Scenario Name]
return
CALCULATE(
SUM('MasterTable'[_Cases]),
FILTER(
ALL('MasterTable'),
'MasterTable'[Year-Period]=_yearperiod&&
'MasterTable'[Source]=_source&&
'MasterTable'[Destination]=_destination&&
MasterTable[PlatformCount]=1
//NOT(CONTAINSSTRINGEXACT('MasterTable'[Scenario Name],"- THIN REG"))
)
)
Because what ever the solution you gave it works for the sample data that i have provided but the thing here is, the string can be anything going forward.
So i calculated PlatformCount and mentioned the condition as it should be 1.
So that i can neglect all the other same duplicated Platform rows.
The problem here is, i am getting same sum value for each Scenario here.
For example, For PC, the sum value is 10 which is corrent but when i choose the Pretzel- THIN REG the sum value should be 12+3 = 15
Because, for choosen Scenario which is Pretzel - THIN REG, we need to consider that row value which is 12 and we have to exclude the other two rows which are same platform i.e Pretzel but same Source and destination
and for PC, based on platform count need to consider the MIN value, so it is 3,
but not PC-Thin Reg.
Any suggestion on this please.
Thanks,
Mohan V.
For Abredeen Source and Broklyn Destination, PC
Hi, @Anonymous
The measure will reflect the selection of slicer, while the column will change only when the data is refreshed and loaded, and cannot interact with other visuals.
You may try to create the following two measures.
PlatformCount =
CALCULATE (
COUNT ( MasterTable[Platform] ),
FILTER (
ALLEXCEPT ( 'MasterTable', MasterTable[Year-Period], MasterTable[Source-Dest] ),
[Index] <= SELECTEDVALUE ( 'MasterTable'[Index] )
&& [Platform] = SELECTEDVALUE ( 'MasterTable'[Platform] )
)
)
Grouped Sum Column =
VAR _yearperiod =
SELECTEDVALUE ( 'MasterTable'[Year-Period] )
VAR _source =
SELECTEDVALUE ( 'MasterTable'[Source] )
VAR _destination =
SELECTEDVALUE ( 'MasterTable'[Destination] )
VAR _Platform =
SELECTEDVALUE ( 'MasterTable'[Platform] )
VAR _Scenario =
SELECTEDVALUE ( MasterTable[Scenario Name] )
RETURN
CALCULATE (
SUM ( 'MasterTable'[_Cases] ),
FILTER (
ALL ( 'MasterTable' ),
'MasterTable'[Year-Period] = _yearperiod
&& 'MasterTable'[Source] = _source
&& 'MasterTable'[Destination] = _destination
&& MasterTable[PlatformCount] = 1
)
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I don't know if I completely understand your question, but I think that this measure does the trick:
(M) Grouped Sum = CALCULATE(SUM('table'[Case]), ALLEXCEPT('table', 'table'[Year], 'table'[Period], 'table'[Source], 'table'[Destination]))
Maybe you can try it out, let me know!
@Anonymous , logic is still not clear to me. Can explain how each line merged
@amitchandak, @Anonymous thanks for the reply.
The output i am expecting, there i am not merging the lines.
i am just trying to show that, how the rows values should be summed.
Year | Period | Platform | Scenario Name | Source | Destination | Case |
2020 | P6 | Pretzel | Pretzel A | Dallas | Houston | 6 + (8 + 9) = 23 |
2020 | P6 | TC | TC | Dallas | Houston | 8 + (6+9) =23 |
Here in first row, 6 is from
2020 | p6 | Pretzel | Pretzel A | Dallas | Houston | 6 |
and 8 is from
2020 | p6 | TC | TC | Dallas | Houston | 8 |
and 9 is from
2020 | P6 | NUTS | NUTS | Dallas | Houston | 9 |
need to show Total sum of Cases for Pretzel A scenario is 23.
here, source,destination, Year-period are same but for 1st Scenario i.e Pretzel A, along with that, i need to consider the other Scenarios which are not Pretzel A but same Source, Destination and Year-Period.
like wise,
2020 | P6 | TC | TC | Dallas | Houston | 8 +(6+9) = 23 |
8 =
2020 | p6 | TC | TC | Dallas | Houston | 8 |
6 =
2020 | p6 | Pretzel | Pretzel A | Dallas | Houston | 6 |
9=
2020 | P6 | NUTS | NUTS | Dallas | Houston | 9 |
2020 | p5 | PC | PC | Dallas | Houston | 7 +(10+11) = 28 |
7 =
2020 | P5 | PC | PC | Dallas | Houston | 7 |
10=
2020 | P5 | DIPS | DIPS | Dallas | Houston | 10 |
11=
2020 | p5 | Pretzel | Pretzel A | Dallas | Houston | 11 |
Hope you understood what i am trying to achive here.
Thanks,
Mohan V.
@Anonymous
Still not 100% sure how you would like to group . Using the measure i mentioned in my previous reply i get these results:
Can you mention which line is wrong and why?
@Anonymous , @amitchandak Sorry, i forgot to add another row in the data where the logic becomes different than the current one.
Please follow the below image,
This is where i was facing the issue.
Thanks,
Mohan V.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |