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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NVNR_01Reddy
Frequent Visitor

PowerBI - Power Query for calculating total %

Hi, 

 

I have data in below format 

 

I have input as below and TotalScoring is something changes per Type

 

 ClientAClientBClientCTotalScoring
Type101270
Type211170
Type322070
Type402070

 

We do the totals per client and results in 3,6,3 and no totals for total scoring

 

An output is needed per client i.e. Total of ClientA/TotalScoring and output as below table 

 

 

NVNR_01Reddy_0-1711032486327.png

 

could someone guide if we can do in DAX or PowerQery 

this is needed in seperate table 

2 ACCEPTED SOLUTIONS
dufoq3
Super User
Super User

Hi @NVNR_01Reddy, there are many ways.

This one should be simple:

 

Your starting table (Source) should be like this:

dufoq3_0-1711032416396.png

 

Result

dufoq3_0-1711032327152.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqksSDVU0lEyAGIQbQTE5gZKsToQKSOoMAwjSRlDVRtBdSNJmUCFkKViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, ClientA = _t, ClientB = _t, ClientC = _t, TotalScoring = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    Transpose = Table.FromRows(Table.ToColumns(#"Demoted Headers")),
    #"Promoted Headers" = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Type] <> "TotalScoring")),
    Ad_ClientSum = Table.AddColumn(#"Filtered Rows", "Client Sum", each List.Sum(List.Transform(Record.ToList(Record.RemoveFields(_, "Type")), Number.From)), type number),
    Ad_Score = Table.AddColumn(Ad_ClientSum, "Score", each [Client Sum] / Number.From(Source{0}[TotalScoring]), Percentage.Type),
    #"Removed Other Columns" = Table.SelectColumns(Ad_Score,{"Type", "Score"})
in
    #"Removed Other Columns"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

Hi @NVNR_01Reddy 

1.The solution @dufoq3  provided is excellent, and you can also refer to the following solution.

Power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqksSDVU0lEyAGIQbQTE5gZKsToQKSOoMAwjSRlDVRtBdSNJmUCFkKViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, ClientA = _t, ClientB = _t, ClientC = _t, TotalScoring = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"ClientA", Int64.Type}, {"ClientB", Int64.Type}, {"ClientC", Int64.Type}, {"TotalScoring", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"(blank)", "TotalScoring"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Score", each List.Sum(Table.SelectRows(#"Unpivoted Columns",(x)=>x[Attribute]=[Attribute])[Value])/[TotalScoring]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Score", Percentage.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Attribute", "Score"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"(blank)", "TotalScoring", "Value"})
in
    #"Removed Columns"

Output

vxinruzhumsft_0-1711076410424.png

2.Dax.

a. You can create a new client table.

vxinruzhumsft_1-1711077534729.png

Then create the following measures.

%per =
SWITCH (
    SELECTEDVALUE ( Client[Client] ),
    "ClientA",
        DIVIDE (
            SUMX ( ALLSELECTED ( 'Table (2)' ), [ClientA] ),
            MAX ( 'Table (2)'[TotalScoring] )
        ),
    "ClientB",
        DIVIDE (
            SUMX ( ALLSELECTED ( 'Table (2)' ), [ClientB] ),
            MAX ( 'Table (2)'[TotalScoring] )
        ),
    "ClientC",
        DIVIDE (
            SUMX ( ALLSELECTED ( 'Table (2)' ), [ClientC] ),
            MAX ( 'Table (2)'[TotalScoring] )
        )
)
Average =
DIVIDE (
    SUMX (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[ClientA] + 'Table (2)'[ClientB] + 'Table (2)'[ClientC]
    ),
    MAX ( 'Table (2)'[TotalScoring] ) * COUNTROWS ( ALLSELECTED ( Client[Client] ) )
)

Output

vxinruzhumsft_2-1711077646263.png

 

Best Regards!

Yolo Zhu

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

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

Hi @NVNR_01Reddy 

1.The solution @dufoq3  provided is excellent, and you can also refer to the following solution.

Power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqksSDVU0lEyAGIQbQTE5gZKsToQKSOoMAwjSRlDVRtBdSNJmUCFkKViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, ClientA = _t, ClientB = _t, ClientC = _t, TotalScoring = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"ClientA", Int64.Type}, {"ClientB", Int64.Type}, {"ClientC", Int64.Type}, {"TotalScoring", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"(blank)", "TotalScoring"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Score", each List.Sum(Table.SelectRows(#"Unpivoted Columns",(x)=>x[Attribute]=[Attribute])[Value])/[TotalScoring]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Score", Percentage.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Attribute", "Score"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"(blank)", "TotalScoring", "Value"})
in
    #"Removed Columns"

Output

vxinruzhumsft_0-1711076410424.png

2.Dax.

a. You can create a new client table.

vxinruzhumsft_1-1711077534729.png

Then create the following measures.

%per =
SWITCH (
    SELECTEDVALUE ( Client[Client] ),
    "ClientA",
        DIVIDE (
            SUMX ( ALLSELECTED ( 'Table (2)' ), [ClientA] ),
            MAX ( 'Table (2)'[TotalScoring] )
        ),
    "ClientB",
        DIVIDE (
            SUMX ( ALLSELECTED ( 'Table (2)' ), [ClientB] ),
            MAX ( 'Table (2)'[TotalScoring] )
        ),
    "ClientC",
        DIVIDE (
            SUMX ( ALLSELECTED ( 'Table (2)' ), [ClientC] ),
            MAX ( 'Table (2)'[TotalScoring] )
        )
)
Average =
DIVIDE (
    SUMX (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[ClientA] + 'Table (2)'[ClientB] + 'Table (2)'[ClientC]
    ),
    MAX ( 'Table (2)'[TotalScoring] ) * COUNTROWS ( ALLSELECTED ( Client[Client] ) )
)

Output

vxinruzhumsft_2-1711077646263.png

 

Best Regards!

Yolo Zhu

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

 

dufoq3
Super User
Super User

Hi @NVNR_01Reddy, there are many ways.

This one should be simple:

 

Your starting table (Source) should be like this:

dufoq3_0-1711032416396.png

 

Result

dufoq3_0-1711032327152.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqksSDVU0lEyAGIQbQTE5gZKsToQKSOoMAwjSRlDVRtBdSNJmUCFkKViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, ClientA = _t, ClientB = _t, ClientC = _t, TotalScoring = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    Transpose = Table.FromRows(Table.ToColumns(#"Demoted Headers")),
    #"Promoted Headers" = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Type] <> "TotalScoring")),
    Ad_ClientSum = Table.AddColumn(#"Filtered Rows", "Client Sum", each List.Sum(List.Transform(Record.ToList(Record.RemoveFields(_, "Type")), Number.From)), type number),
    Ad_Score = Table.AddColumn(Ad_ClientSum, "Score", each [Client Sum] / Number.From(Source{0}[TotalScoring]), Percentage.Type),
    #"Removed Other Columns" = Table.SelectColumns(Ad_Score,{"Type", "Score"})
in
    #"Removed Other Columns"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors