Forum Discussion
Get Matrix row count ignore columns
I am trying to create a report with Average of scores, but I also want to show number of people with the scores in the visual.
Sample Data:
GenderItermScoreYear
| M | E1 | 4.5 | 2020 |
| M | E1 | 4.4 | 2020 |
| M | E1 | 4.5 | 2021 |
| M | E2 | 4.3 | 2021 |
| M | E2 | 4.5 | 2021 |
| F | E1 | 4.2 | 2021 |
| F | E1 | 4.5 | 2020 |
| F | E1 | 4.6 | 2021 |
| F | E1 | 4.5 | 2020 |
| F | E1 | 4.3 | 2021 |
| F | E1 | 4.4 | 2021 |
| F | E2 | 4.3 | 2021 |
| F | E1 | 4.2 | 2020 |
| F | E2 | 4.6 | 2021 |
| F | E2 | 4.6 | 2021 |
| F | E1 | 4.2 | 2021 |
| F | E2 | 4.5 | 2020 |
| F | E2 | 4.3 | 2021 |
My visual looks like this:
| Gender | E1 | E2 |
| F | 4.36 | 4.46 |
| M | 4.47 | 4.4 |
These are Average of scores, but I also want to add the row count.
| Gender | Count of score |
| F | 13 |
| M | 5 |
So ultimately, it should look like this:
| Gender | Count | E1 | E2 |
| F | 13 | 4.36 | 4.46 |
| M | 5 | 4.47 | 4.4 |
Also need to preserve different filters/slicers. In this case, Year.
Hi chirag_usf ,
Bring the Year field to the front
Here are the steps you can follow：
1. Create calculated table.
Table_1 = var _summ= SUMMARIZE( 'Table', 'Table'[Year],'Table'[Genderl], "term","Count", "Sorce",COUNTX(FILTER(ALL('Table'),'Table'[Genderl]=EARLIER('Table'[Genderl])),[Genderl])) return UNION('Table',_summ)Table_2 = SUMMARIZE( 'Table_1', 'Table_1'[Year],'Table_1'[Genderl],'Table_1'[term], "Score", IF( 'Table_1'[term] IN {"E1","E2"}, SUMX(FILTER(ALL('Table_1'),'Table_1'[term]=EARLIER('Table_1'[term])&&'Table_1'[Genderl]=EARLIER('Table_1'[Genderl])),[Score]) / COUNTX(FILTER(ALL('Table_1'),'Table_1'[term]=EARLIER('Table_1'[term])&&'Table_1'[Genderl]=EARLIER('Table_1'[Genderl])),[term]) , SUMX(FILTER(ALL('Table_1'),'Table_1'[term]=EARLIER('Table_1'[term])&&'Table_1'[Genderl]=EARLIER('Table_1'[Genderl])),[Score]) /2 ) )2. Place it in the matrix Visual and set the Values field to Average.
3. Result.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
- Greg_Deckler
Community Champion
chirag_usf You could add a COUNTROWS measure to your visual. Then you would have to turn off word wrap and shrink up your columns. The alternative is to use a custom matrix hierarchy:
The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community
One other thought, you could use a Table visual and create a measure for E1 and E2 as well as the COUNTROWS measure.
- tamerj1
Hi chirag_usf
Please refer to sample file withthe solution https://we.tl/t-ljhQEeEFHdAverage Score = IF ( ISFILTERED ( Data[Iterm] ), AVERAGE ( Data[Score] ), FORMAT ( CALCULATE ( COUNTROWS ( Data ), ALL ( Data[Iterm] ) ), "#" ) ) Hi chirag_usf ,
Bring the Year field to the front
Here are the steps you can follow：
1. Create calculated table.
Table_1 = var _summ= SUMMARIZE( 'Table', 'Table'[Year],'Table'[Genderl], "term","Count", "Sorce",COUNTX(FILTER(ALL('Table'),'Table'[Genderl]=EARLIER('Table'[Genderl])),[Genderl])) return UNION('Table',_summ)Table_2 = SUMMARIZE( 'Table_1', 'Table_1'[Year],'Table_1'[Genderl],'Table_1'[term], "Score", IF( 'Table_1'[term] IN {"E1","E2"}, SUMX(FILTER(ALL('Table_1'),'Table_1'[term]=EARLIER('Table_1'[term])&&'Table_1'[Genderl]=EARLIER('Table_1'[Genderl])),[Score]) / COUNTX(FILTER(ALL('Table_1'),'Table_1'[term]=EARLIER('Table_1'[term])&&'Table_1'[Genderl]=EARLIER('Table_1'[Genderl])),[term]) , SUMX(FILTER(ALL('Table_1'),'Table_1'[term]=EARLIER('Table_1'[term])&&'Table_1'[Genderl]=EARLIER('Table_1'[Genderl])),[Score]) /2 ) )2. Place it in the matrix Visual and set the Values field to Average.
3. Result.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
