DAX measure Suggestion

[ahmetturetmis]

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 !!

[ahmetturetmis]

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.

[v-shex-msft · Accepted 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

[v-shex-msft]

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

[amitchandak]

@ahmetturetmis , can you share actual formula

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