Forum Discussion

trndlnd's avatar
trndlnd
Frequent Visitor
1 year ago
Solved

Percentage calculation

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?

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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's avatar
    Tahreem24
    Super User
    1 year ago

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

     

  • nirali_arora's avatar
    nirali_arora
    Resolver II
    1 year ago

    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)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.