Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello All,
I would like to ask a measure formula because I couldn't write properly. I miss some little details but I can't see where.
You can see the table below. I add some Letters on top the columns in order to make it easier to explain what I plan to. I dont have to show all the columns but I need to see the results of each calculation thats why I increased the column amount.
Only A and C columns are not measures rest of them are measure.
In summary I try to distribute Mh values of some CBS Id (From CBS) to Other Selected CBS Id (Focus CBS) with a dynamic weight. I have problem to calculate this dynamic weight.
This dynamic weight should be calculated by the amount of the Focus CBS Quantities for example between the green lines:
% Focus weight is given by me in terms of the importance of the focus activity.
Weight percentage is calculated by the quantitty of Focus CBS / total Focused CBS
Last factor Nominator is % Focus Weight * Weight percentage
Last Factor Denominated is the place that I cannot calculate correctly. For example It should be the total of the 0.09 + 0.13 + 0.07 = 0.29 then I will divide 0.09/0.29 and get the last Percentage.
Thanks a lot in advance for your help !!
Solved! Go to Solution.
Hi @ahmetturetmis,
You can use following calculate table formula to create a new table that summary raw table value, it should dynamically change based on the raw 'calculation table':
summary =
GROUPBY (
SUMMARIZE (
T2,
[FROM CBS],
[FOCUS CBS],
[MH],
"Rate", [Final Weightage] * [MH]
),
[FOCUS CBS],
"Total", SUMX ( CURRENTGROUP (), [Rate] )
)
Regards,
Xiaoxin Sheng
I've prepared a dummy table :
Data table this is changing everyday.
Date | CBS | Quantity | MH |
01-09-20 | A | 1 | 12 |
01-09-20 | B | 2 | 15 |
01-09-20 | C | 3 | 18 |
02-09-20 | D | 4 | 21 |
03-09-20 | E | 5 | 24 |
02-09-20 | F | 6 | 27 |
02-09-20 | G | 7 | 30 |
02-09-20 | H | 8 | 33 |
Total | 180 |
This is a Importance matrix
A | B | E | |
A | 1 | ||
B | 1 | ||
C | 2 | 1 | |
D | 1 | 1 | |
E | 1 | ||
F | 1 | 1 | |
G | 1 | ||
H | 1 |
This is the Calculation Table
FROM CBS | FOCUS CBS | FROM CBS MH | FROM CBS QTY | QTY WEIGHT | IMPORTANCE | IMPORTANCE WEIGHT | QTY WEIGH X IMPORTANCE WEIGHT | Totals of Combination | Final Weightage | MH | DISTRIBUTED MH |
A | A | 12 | 1 | 100% | 1 | 100% | 100% | 100% | 100% | 12 | 12.00 |
B | B | 15 | 2 | 100% | 1 | 100% | 100% | 100% | 100% | 15 | 15.00 |
C | A | 18 | 1 | 17% | 2 | 67% | 11% | 39% | 29% | 18 | 5.14 |
C | E | 18 | 5 | 83% | 1 | 33% | 28% | 39% | 71% | 18 | 12.86 |
D | A | 21 | 1 | 100% | 1 | 50% | 50% | 64% | 78% | 21 | 16.33 |
D | B | 21 | 2 | 29% | 1 | 50% | 14% | 64% | 22% | 21 | 4.67 |
E | E | 24 | 5 | 71% | 1 | 100% | 71% | 71% | 100% | 24 | 24.00 |
F | A | 27 | 1 | 33% | 1 | 50% | 17% | 50% | 33% | 27 | 9.00 |
F | B | 27 | 2 | 67% | 1 | 50% | 33% | 50% | 67% | 27 | 18.00 |
G | E | 30 | 5 | 100% | 1 | 100% | 100% | 100% | 100% | 30 | 30.00 |
H | B | 33 | 2 | 100% | 1 | 100% | 100% | 100% | 100% | 33 | 33.00 |
Total | 180 |
This is the Summary of Focus CBS
FOCUS CBS | DISTRIBUTED MH |
A | 42.48 |
B | 70.67 |
E | 66.86 |
Total | 180 |
This calculations are changing with the new quantity and mh values added.
Hi @ahmetturetmis,
You can use following calculate table formula to create a new table that summary raw table value, it should dynamically change based on the raw 'calculation table':
summary =
GROUPBY (
SUMMARIZE (
T2,
[FROM CBS],
[FOCUS CBS],
[MH],
"Rate", [Final Weightage] * [MH]
),
[FOCUS CBS],
"Total", SUMX ( CURRENTGROUP (), [Rate] )
)
Regards,
Xiaoxin Sheng
Hi @ahmetturetmis,
So you mean you want to create a measure that can get the diff based on selection ranges and current category type?
If this is a case, you can write a measure with a variable to extract the special ranges and summarize that ranges with your measure formulas based on the index fields. (you can't directly use two text values to find out the records between the selection records)
measure =
VAR _start =
MIN ( table[Index] )
VAR _end =
MAX ( selector[Index] )
VAR currLFD =
CALCULATE (
[Last Factor Denominated],
ALLSELECTED ( table ),
VALUES ( table[From Cbs] )
)
VAR summary =
SUMMARIZE (
FILTER ( ALLSELECTED ( table ), [Index] >= _start && [Index] <= _end ),
[From Cbs],
"LFD", [Last Factor Denominated]
)
RETURN
currLFD / SUMX ( summary, [LFD] )
In addition, it will help if you share some dummy data with your formulas for future testing.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@ahmetturetmis , can you share actual formula
Can you share sample data and sample output in table format?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
98 | |
41 | |
38 |
User | Count |
---|---|
151 | |
123 | |
79 | |
73 | |
71 |