PowerBI - Power Query for calculating total %

[NVNR_01Reddy]

Hi, 

I have data in below format 

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

	ClientA	ClientB	ClientC	TotalScoring
Type1	0	1	2	70
Type2	1	1	1	70
Type3	2	2	0	70
Type4	0	2	0	70

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 

could someone guide if we can do in DAX or PowerQery 

this is needed in seperate table 

[dufoq3 · Accepted solution]

Hi @NVNR_01Reddy, there are many ways.

This one should be simple:

Your starting table (Source) should be like this:

Result

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"

[Anonymous · Accepted solution]

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

2.Dax.

a. You can create a new client table.

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

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 · Accepted solution]

Hi @NVNR_01Reddy, there are many ways.

This one should be simple:

Your starting table (Source) should be like this:

Result

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"