topic Re: How to calculate to dynamic weighted average for selected filter. in DAX Commands and Tips

How to calculate to dynamic weighted average for selected filter.

Anonymous — Fri, 12 Aug 2022 16:19:15 GMT

Hi Experts,

could you please help me to get resolve problem of calculating dynamic Weightage depdending upon slicer value change

in my case (Year & Quarter is slicer)

Scenario is

For the calculations we need to include a dynamic weighting structure where for whatever time period is selected, we only include non-zero data points and we recalculate the weighted average using total outstanding amount only for those companies that have a value in that time period. So for example, a company that has a ratio of non-zero would be included and others with a zero or blank would be excluded. The total weighted average would only be on those included companies.

Alos please consider we have multi year and quarter data included in this table so when user selectes no filter then only distinct sum of outstanding amount should consider.

Company Name	Ratio	Outstanding Amount	Current Weightage (Outstanding amount / Sum(Outstanding amount ))	Expected Weightage (Outstanding Amount / Sum(Outstanding Amount where ratio is not null and non zero ))	Current Ratio	Expected Ratio by Weightage (Ratio * Expected weightage )	Year Quarter
A	0.00	2906415	0.0068004		0	0	Q1-2022
B	0.00	2906415	0.0068004		0	0	Q1-2022
C	0.00	61307707	0.143448		0	0	Q1-2022
D	0.00	78021858	0.1825558		0	0	Q1-2022
E	0.00	75517287	0.1766956		0	0	Q1-2022
F	71.96	9803750	0.0229388	0.09	1.650786131	6.740188497	Q1-2022
G	37.84	43935077	0.1027994	0.42	3.890138351	15.88350258	Q1-2022
H	0.00	40620349	0.0950436		0	0	Q1-2022
I	0.00	61432161	0.1437392		0	0	Q1-2022
J	31.33	50935312	0.1191786	0.49	3.733678687	15.24467505	Q1-2022

Could you please advise how can I respect slicer filter as well as distinct sum of outstanding amount.

Regards,

Niraj Sevalkar

Re: How to calculate to dynamic weighted average for selected filter.

daXtreme — Sat, 13 Aug 2022 09:45:01 GMT

Hi there

According to your description, the data you give is not representative of the problem.

Re: How to calculate to dynamic weighted average for selected filter.

Anonymous — Tue, 16 Aug 2022 08:55:38 GMT

Please let me know where you see problem.

Regards,

Niraj Sevalkar

Re: How to calculate to dynamic weighted average for selected filter.

Anonymous — Wed, 17 Aug 2022 02:50:59 GMT

Hi Anonymous ,

I can't upload pbix files due to special reasons. Please refer to my steps. Input the codes first.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZE5cgQhDEXv0vGY+tqQFHpfMsddc/9rWHQzDsZVBA4ohOCJ/6V93x63y4ZanOhKNk4N6AFoxfNyrG96YDCP5PWyb0//5J5nupPAHX6ApKIaS+5lpj3AFBYnF2xma/D1BpqRc8wPvfe0vgTfBkQtx6sMiBtOl8wpcQpA1katGzzKEQ1jzRUUoel/a75XIN5i9EglpbgpCOyZesQ6nkuLBEmIjaJkLSoEH2bvin5M/YrOEM2pzKCydvj5OwsVpk63WbgkL8GvIZCaSO2GsiHEJ0xJ1YnTRh42XKRX6mh82WDVXp20u6LXHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#(lf)Company Name" = _t, Ratio = _t, #"Outstanding Amount" = _t, #"Current Weightage#(lf)#(lf)(Outstanding amount / Sum(Outstanding amount ))" = _t, #"Expected Weightage (Outstanding Amount / Sum(Outstanding Amount where ratio is not null and non zero ))" = _t, #"Current Ratio" = _t, #"Expected Ratio by Weightage #(lf)#(lf)(Ratio * Expected weightage )" = _t, #"Year Quarter" = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"#(lf)Company Name", type text}, {"Ratio", type number}, {"Outstanding Amount", Int64.Type}, {"Current Weightage#(lf)#(lf)(Outstanding amount / Sum(Outstanding amount ))", type number}, {"Expected Weightage (Outstanding Amount / Sum(Outstanding Amount where ratio is not null and non zero ))", type number}, {"Current Ratio", type number}, {"Expected Ratio by Weightage #(lf)#(lf)(Ratio * Expected weightage )", type number}, {"Year Quarter", type text}, {"Column1", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}) in #"Removed Columns"

Then create 2 measures.

EXPECTED weigtted = VAR _outstanding = MAX ( 'Table'[Outstanding Amount] ) VAR _zero = IF ( MAX ( 'Table'[Ratio] ) = 0 || MAX ( 'Table'[Ratio] ) = BLANK (), 0, MAX ( 'Table'[Outstanding Amount] ) ) VAR _hou = CALCULATE ( SUM ( 'Table'[Outstanding Amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Ratio] <> 0 || 'Table'[Ratio] <> BLANK () && 'Table'[Year Quarter] = SELECTEDVALUE ( 'Table'[Year Quarter] ) ) ) VAR _result = _outstanding / _hou RETURN IF ( MAX ( 'Table'[Ratio] ) = 0 || MAX ( 'Table'[Ratio] ) = BLANK (), BLANK (), _result )

expect ratio = MAX('Table'[Ratio])*[EXPECTED weigtted]

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.