Forum Discussion
Please help - percentage across multiple rows
Hi there,
I hope you can help me - I have been pulling my hair out for days! I think what I want to do should be relatively simple, but I really am struggling to work it out.
My dataset looks like the table 1 below (just with 300+ projects rather than 3), with each project having multiple rows. I basically want to find a way to work out the percentage of customer requested for each project, so I would get the percentage values shown in Table 2.
Just to make it even more tricky, I want to be able to use the result of this to filter other visuals, e.g. to only include projects that have more than 75% Customer Requested request type.
Table 1. What I have now....
| Project Name | Request Type |
| Project A | Customer Requested |
| Project A | Customer Requested |
| Project A | Company Requested |
| Project A | Customer Requested |
| Project B | Customer Requested |
| Project B | Company Requested |
| Project C | Customer Requested |
| Project C | Customer Requested |
Table 2. What I want to have...
| Project Name | % Customer Requested |
| Project A | 75% Customer Requested |
| Project B | 50% Customer Requested |
| Project C | 100% Customer Requested |
Thank you so much for any help that you can provide!
amitchandak I have just updated - I hope this helps 🙂
Hi Anonymous ,
Is the image below what you intend?
If yes here is the m-query code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRci4tLsnPTS1SCEotLE0tLklNUYrVQShxIqzEmbASCiyKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Customer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Customer", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Project", "Customer"}, {{"Test", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"And for the last step just create a metric in this format:
% of Grand Total =DIVIDE (SUM( 'Table 1'[Test] ),CALCULATE ( SUM ( 'Table 1'[Test]), ALL ( 'Table 1'[Project]) ))You will be able to then stick it as filter page, visual or entire report.Best Regards,IDHi Anonymous ,
You could use the following formula to create a calculated table :
New Table = SUMMARIZE ( 'Table', [Project Name], "% Customer Requested", VAR _count = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Project Name] ) ) VAR _custom = CALCULATE ( COUNT ( 'Table'[Request Type] ), FILTER ( 'Table', 'Table'[Request Type] = "Customer Requested" ) ) RETURN FORMAT ( DIVIDE ( _custom, _count ), "##%" ) & " Customer Requested" )The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi Anonymous ,
Is the image below what you intend?
If yes here is the m-query code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRci4tLsnPTS1SCEotLE0tLklNUYrVQShxIqzEmbASCiyKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Customer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Customer", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Project", "Customer"}, {{"Test", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"And for the last step just create a metric in this format:
% of Grand Total =DIVIDE (SUM( 'Table 1'[Test] ),CALCULATE ( SUM ( 'Table 1'[Test]), ALL ( 'Table 1'[Project]) ))You will be able to then stick it as filter page, visual or entire report.Best Regards,ID- amitchandak
Super User
Anonymous , the column name is not clear. can you give column heading to both tables?
We can copy data from one table to another by 4 method
Hi amitchandak,
Thank you for your help, but I don't need to copy data from one table to another, I need to work out the percentage of request types are Customer Requested for each project.
Hi Anonymous ,
You could use the following formula to create a calculated table :
New Table = SUMMARIZE ( 'Table', [Project Name], "% Customer Requested", VAR _count = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Project Name] ) ) VAR _custom = CALCULATE ( COUNT ( 'Table'[Request Type] ), FILTER ( 'Table', 'Table'[Request Type] = "Customer Requested" ) ) RETURN FORMAT ( DIVIDE ( _custom, _count ), "##%" ) & " Customer Requested" )The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
