Please help - percentage across multiple rows

[Anonymous]

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 🙂 

[Anonymous · Accepted 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:

% 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

[v-eqin-msft]

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

[v-eqin-msft · Accepted solution]

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.

[Anonymous · Accepted 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:

% 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

[Anonymous]

Thank you so much - I will try now

[amitchandak]

@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

[Anonymous]

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.