cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## How to calculate to dynamic weighted average for selected filter.

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

1 ACCEPTED SOLUTION
Community Support

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]

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.

3 REPLIES 3
Community Support

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]

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.

Solution Sage

Hi there

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

Anonymous
Not applicable

Please let me know where you see problem.

Regards,

Niraj Sevalkar

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors