Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
.
Hi @Anonymous
Not very sure. I create a sample according to your description, could you show your expected result based on the sample? So that we can write a measure for you.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Two comments:
- ALLEXCEPT removes ALL filters EXCEPT the category filters. Including your Country filter. Likely not what you want.
- SUMMARIZE is dangerous-ish. It may not do what you think it does. All the secrets of SUMMARIZE - SQLBI
Thanks for the heads up - would you be able to help me rewrite the measure? I am not sure which other avenues are left
Please provide sanitized sample data that fully covers your issue. Please show the expected outcome.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |