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.
Please help
I have a table whose one column is the ID and other formula (can be different lengths):
ID Formula Name
1 40-50+2025 Reason1
2 30-30+45+60-789 Reason2
......
Also I have an account table:
ID account amount BussinesUnitID
1 40 200 1
2 402 100 3
3 50 20 2
4 2025 30 3
5 30 60 2
6 45 70 2
7 4036 90 1
8 301 40 2
9 60 50 1
10 789 20 1
.....
From all of this I need to get table like that: (but must respond to the selected bussines units in the filter)
If all bussines units are selected in the filter
Name amount
Reason1 400
Reason2 100
How it should be calculated:
Reason1 have formula 40-50+2025 so
40 (all accounts whose start numbers like 40 sum) -- > 200+100+90+390
50 (all accounts whose start numbers like 50 sum) --> 20
2025 (all accounts whose start numbers like 2025 sum) --> 30
So by formula 40-50+2025 reason1 will be 400 (390-20+30)
But if just a few bussines units are selected ( like 1 ,2 ) table shoud be:
Name amount
Reason1 270
Reason2 100
How it should be calculated:
Reason1 have formula 40-50+2025 so
40 (all accounts whose start numbers like 40 sum and BussinesUnitID is 1 or 2) -- > 200+90+290
50 (all accounts whose start numbers like 50 sum and BussinesUnitID is 1 or 2) --> 20
2025 (all accounts whose start numbers like 2025 sum and BussinesUnitID is 1 or 2) --> 0
So by formula 40-50+2025 reason1 will be 270 (290-20+0)
Have any ideas how to do this? 😞
@Anonymous , Try a measure like
calculate(sum(Table[amount]), filter(Table, left(Table[account],2) = "40"))
- calculate(sum(Table[amount]), filter(Table, left(Table[account],2) = "50"))
- calculate(sum(Table[amount]), filter(Table, left(Table[account],4) = "2025"))
Or create a column like this sum that up
reason 1 = Switch( True() ,
left(Table[account],4) = "2025" || left(Table[account],2) = "40" , 1*Table[amount] ,
left(Table[account],2) , -1 * Table[amount]
)
reason 1 measure = sumx(Table, Switch( True() ,
left(Table[account],4) = "2025" || left(Table[account],2) = "40" , 1*Table[amount] ,
left(Table[account],2) , -1 * Table[amount]
))
The problem is that in first table there can be many values, and it's not enough to count just reason1.. I need universal formula which would fit all the rows in the column and calculate reason1, reason2, reason3 .... reasonN. First table have many different values like:
ID Formula Name
1 40-50+2025 Reason1
2 30-30+45+60-789 Reason2
3 50+60-90+40 Reason3
4 458+68-698 Reason4
5 45+60-90+98-85+65 Reason5
6 45+60-789 Reason6
N 45+-8960-789 ReasonN
And I need that it respond to a filter BussinesUnitID where the values are chosen by the person himself in visualizations slicer
@Anonymous , every reason has different rule. are those rules stored somewhere ? If not I doubt yu can make it generic
No, they have the same rules just have to pick up different account numbers and there are different numbers of items. They are stored in the first table as I tried to show in the example
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 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |