PowerBI/DAX: sum of detail table

0
votes

I am designing a PowerBI report using DirectQuery on a database. In the database I have tables for persons and scores. Every person has one or more scores. The total of the scores is always between 1 and 7. Example:

Persons
-------
Anna
Jane
John
Luis

Persons  Score
-----------------
Anna      1
Anna      2
Jane      1
Jane      2
Luis      1
Luis      2
Luis      4

I would like to see something like:

Score  Count
------------
 7      1
 3      2

Meaning, there is one person with 7 points and 2 people with 3 points.

How do I do that in PowerBI/DAX?

1
powerbidax

1 Answers

1
votes

If Persons_Scores is what you are really getting as a table in Power BI then you can go to "Edit Queries" section and do the grouping and counting from there. Below you can find steps for doing this. 1. After going to the edit queries and selecting table with scores you choose highlighted Group by option enter image description here

  1. You will see options for how you want to group your data. You select Name column for grouping and you applu SUM function for the score column and name it e.g. ScoreSum enter image description here

  2. You will get sum of scores for each person as below. enter image description here

  3. Then you apply another grouping but this time you group by ScoreSum and put a count function on the name column. The process is very similiar so I do not attach screenshots. In the end you will get desired table by hitting Close & Apply at the top left