Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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
v-rongtiep-msft
Community Support
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]

 

vpollymsft_0-1660704435453.png

 

 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.

View solution in original post

3 REPLIES 3
v-rongtiep-msft
Community Support
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]

 

vpollymsft_0-1660704435453.png

 

 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
Solution Sage
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.