- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"

Helpful resources
Power BI Monthly Update - April 2025
Check out the April 2025 Power BI update to learn about new features.
NEW! Community Notebooks Gallery
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
