Percentage calculation

[trndlnd]

Hi everyone,

I have a SharePoint-list with 155 columns (different areas) that shows either Red or Green. Beside these columns I have an ID-column (from forms) and a date column.

I’ve unpivoted the Area-columns in Power Query so that I have a column that shows Area and a column that shows status (red/green – only showing red though), and of course a column for ID and a column for the date.

I want to show in percentage how many times red occur on each area, based on the ID-column.

I.E – The list has 4 registrations. “Area 1” has 1 red registration in those 4 lines. “Area 2” has 3 red in those 4 lines.

Area 1 should show 25%

Aree 2 should show 75%.

I guess I only need a simple calculation to get my result?

[Anonymous · Accepted solution]

Thanks for the reply from Tahreem24, Kedar_Pande and nirali_arora, please allow me to provide another insight.
Hi @trndlnd ,

I am not sure how your source data table is designed, below is my sample.

The source data table ( transformed in Power Query)

Creates the measure to be used to calculate the percentage of Red.

Percentage of red =
VAR total_for_each_area = COUNTROWS('Table')
VAR red_in_each_area = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Red")
RETURN
DIVIDE(red_in_each_area,total_for_each_area)

Putting this measure in the matrix shows the percentage of red in each area. Hopefully this will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[nirali_arora]

You can try one of the below measures:
M1= 
DIVIDE(CALCULATE(COUNTROWS(Data), Data[Status] = "Red"), COUNTROWS(Data), 0)

M2 = 
DIVIDE(CALCULATE(COUNTROWS(Data), Filter( Data, Data[Status] = "Red")), CALCULATE(COUNTROWS(Data), allexcept(Data, Data[Area], 0)))

M3 = 
DIVIDE(CALCULATE(COUNTROWS(Data), Filter( Data, Data[Status] = "Red")), CALCULATE(COUNTROWS(Data), removefilters( Data[Status], 0)))

[Kedar_Pande]

@trndlnd 

Red Percentage = 
DIVIDE(
COUNTROWS(
FILTER(
'YourTable',
'YourTable'[Status] = "Red"
)
),
DISTINCTCOUNT('YourTable'[ID])
) * 100

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Tahreem24]

@trndlnd  Kindly explain with the help of sample dummy data in excel with expected output.