Forum Discussion
DAX measure Suggestion
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 !!
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
4 Replies
ahmetturetmis , can you share actual formula
Can you share sample data and sample output in table format?
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
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
