cancel
Showing results for
Did you mean:

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.

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
4 REPLIES 4
Frequent Visitor

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.

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

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.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

@ahmetturetmis , can you share actual formula

Can you share sample data and sample output in table format?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors