How to calculate to dynamic weighted average for selected filter.

[nirajsevalkar]

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

[v-rongtiep-msft · Accepted solution]

Hi @nirajsevalkar ,

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.

[daXtreme]

Hi there

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

[nirajsevalkar]

Please let me know where you see problem.

Regards,

Niraj Sevalkar