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

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