Calculate a row percentage based on Matrix columns

[Anonymous]

Hi I need help.

I'm trying to turn this matrix

Into this matrix

I want to be able to calculate the percentage completed for jan, feb, mar and so on while being able to exclude a specific row from the calculation. for example, montreal should not have data for mar so the overall percentage for mar should be based 4 cities instead of 5. Is this possible in power bi?

Thank you all for your assistance.

[Anonymous]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table1 =
var _table1=
SUMMARIZE('Table','Table'[Date],
"cities","%completed",
"Amount",
FORMAT(
DIVIDE(
COUNTX(FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Amount]<>0),[Amount]),
COUNTX(FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),[cities])),"Percent"))
return
UNION('Table',_table1)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @Anonymous 

Thanks so much for your response. The solution you gave looks great and as I can see it produced my desired results.

However, it'll be a bit difficult for me to reproduce without ome tweeks. Yours are all from one table, mine are from 3 different tables.

So for the first part of my issue which is simpler than the second part. Using the same scenario, cities is in one table, months from a date table, and the amount is a measure calculated for yet another table. Can you please point me in the right direction on how to put these tweeks in, thanks so much.

Also, in your data, can you make Montreal for march to be null (nothing) not 0 so it doesn't get counted when computing the %completed for that month.

[Anonymous]

Thanks for your response @Ashish_Mathur. Unfortunately I cant share the PBI file due to privacy concerns, wish I could. But can you give me a step by step on how I can do this if its possible? Thanks so much for your assistance.

[Ashish_Mathur]

Share anonymised data.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.