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
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.
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"
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.
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.
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
62 | |
18 | |
16 | |
13 |