Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to Solution.
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:
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.
Hi @Anonymous ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.
Best Regards,
Eyelyn Qin
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.
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:
Thank you so much - I will try now
@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
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |