Forum Discussion

kangkopi15's avatar
kangkopi15
Icon for Helper I rankHelper I
2 years ago
Solved

Percentage of value

Dear experts, I am quite new to DAX, I need a little advice. I have this table, and I need to make a percentage of the score. The output would be Score 1 = 14.28% Score 2 = 42.86% and so on..  ...
Need Help
  • collinsg's avatar
    collinsg
    2 years ago

    Good day kangkopi15,

    It looks like you want to find the percentage of scores which are 1, 2...5. If this is the case you can create a matrix visual, put "Score" in rows and use this measure for the values.

    Percentage frequency of score =
    VAR thisScore = SELECTEDVALUE(tbl[Score])
    VAR countOfAllScores = CALCULATE(COUNTROWS('tbl'),REMOVEFILTERS('tbl'))
    VAR countOfScore = CALCULATE(COUNTROWS('tbl'),REMOVEFILTERS('tbl'),'tbl'[Score]=thisScore)
    RETURN DIVIDE(countOfScore, countOfAllScores)

    Hope this helps

collinsg's avatar
collinsg
Icon for Solution Sage rankSolution Sage
2 years ago

Good day kangkopi15,

It looks like you want to find the percentage of scores which are 1, 2...5. If this is the case you can create a matrix visual, put "Score" in rows and use this measure for the values.

Percentage frequency of score =
VAR thisScore = SELECTEDVALUE(tbl[Score])
VAR countOfAllScores = CALCULATE(COUNTROWS('tbl'),REMOVEFILTERS('tbl'))
VAR countOfScore = CALCULATE(COUNTROWS('tbl'),REMOVEFILTERS('tbl'),'tbl'[Score]=thisScore)
RETURN DIVIDE(countOfScore, countOfAllScores)

Hope this helps

kangkopi15's avatar
kangkopi15
Icon for Helper I rankHelper I
2 years ago

I got syntax error

  • collinsg's avatar
    collinsg
    Icon for Solution Sage rankSolution Sage
    2 years ago

    kangkopi15

    1. Did you replace "tbl" with the name of your table?
    2. Is your "Score" column a number type?

    The syntax is correct and gives the answers to the problem.

    Hope this helps

    • kangkopi15's avatar
      kangkopi15
      Icon for Helper I rankHelper I
      2 years ago

      hi collin, my bad the syntax is correct, i didn't change the column name, however how can I change that to percentage?

      • collinsg's avatar
        collinsg
        Icon for Solution Sage rankSolution Sage
        2 years ago

        Good day kangkopi15,

        If you are working in Power BI Desktop there are several ways of presenting a measure as a percentage. The most straightfoward is to use the format drop-down on the modelling ribbon. For example, I created a simple measure for demonstration purposes, Percent = 1/3, then formatted it as "Percentage" with 2 decimal places.

        the procedure is similar if working in Excel,

        Hope this helps