Hi Power BI geniuses out there,
I'm currently working on a measure where I am trying to create a matrix for each category and brand with relative scores. I have been struggling with this for as long as I can remember. The raw data table looks like this:
| PersonId | Country | Brand | Category | Score |
| 1 | 3 | 1 | 1 | 1 |
| 1 | 3 | 1 | 2 | 0 |
| 1 | 3 | 1 | 3 | 1 |
| 1 | 3 | 1 | 4 | 0 |
| 1 | 3 | 1 | 5 | 0 |
| 1 | 3 | 2 | 1 | 1 |
The measure works as follows:
- calculate average score for each category/brand
- calculate the column total score for a brand
- calculate the row total score for category
- sum all the row total (or column total scores, doesnt matter it will be the same) for category
relative score is = average score - weighted score
where weighted score = column total*row total/all row(or column) totals
see example below:
| | | | Brands | | |
| | | brand 1 | brand 2 | brand 3 | |
| | category 1 | 60 | 50 | 10 | 120 |
| Categories | category 2 | 40 | 30 | 45 | 115 |
| | category 3 | 30 | 20 | 35 | 85 |
| | sum | 130 | 100 | 90 | row total sum |
| | | | | | 320 |
| | | | | | |
| | | | Brands | | |
| | | brand 1 | brand 2 | brand 3 | |
| | category 1 | each cell = (relevant row level sum - relevant column level sum ) / total sum of all rows | |
| Categories | category 2 | |
| | category 3 | |
| | | | | | |
Now I have created some measures to calculate row/column totals with the following methodology, example for all the score for attributestotals:
sum of all attributes = CALCULATE(Sumx(summarize(Table, Table[BrandId], Table[Category], "_avg", Average(Table[Score])),[_avg]),ALLEXCEPT(Table,Table[Category]))
However, this measure only works if I don't apply any filters. Now for example if I want to apply a filter on the country, it will calculate using all category scores and all brands scores. Ofcourse not all categories are in all countries and all brands in all countries. I am wondering if there is a way for me to set up this measure so it will still work with a country level filter? I have been struggling with this measure for so long so I would appreciate any help. In essence I am trying to ignore the row and column level context of the matrix in which the measure is placed in as a value, but still be able to slim the dataset down on country level as a filter. Any help is appreciated so much!
Kind regards,
Deniz
.
