Forum Discussion
PowerBi Matrix Table - Compare 2 different variables of same object
| C1 | 1 | 0 |
| C2 | 2 | 1 |
| C3 | 4 | 3 |
| C4 | 5 | 4 |
| C5 | 6 | 5 |
| C6 | 4 | 3 |
| C7 | 3 | 2 |
| C8 | 2 | 1 |
| C9 | 2 | 1 |
| C10 | 1 | 0 |
Problem is when I put table into a matrix (pivot table) my total is always 10 which is fine but my count of apples is always the same in relation to how many people have 1 or 2 apples if i use "Apples" column and put values as Apples and Apples-1. Eg output would look like:
| 1 | 2 | 2 |
| 2 | 3 | 3 |
| 3 | 1 | 1 |
| 4 | 2 | 2 |
| 5 | 1 | 1 |
| 6 | 1 | 1 |
Desired Pivot/Matrix result (2nd and 3rd columns are value counts)
| 0 | 0 | 2 |
| 1 | 2 | 3 |
| 2 | 3 | 1 |
| 3 | 1 | 2 |
| 4 | 2 | 1 |
| 5 | 1 | 1 |
| 6 | 1 | 0 |
I have tried all sorts but can't figure out how to get the desired count in column 2 and 3 of pivot. used Apples-1 to get 0 to 6 in rows but result is same in that count is same for both 2nd and 3rd column! What do I need to do to get the 0 in first (Apples) column if using "Apples" as Rows. tried to duplicate Table and connect with Dim table that has 1 to 6 but that got confusing quick and didn't work! Any ideas, what am I doing wrong!!! I get that it's something to do with IDs but how do I get it to adjust?
Hi ES_TH
For your question, here is the method I provided:
First, go to the power query and Unpivot Columns for Apples and Apples-1.
Create a measure.
Measure = CALCULATE( COUNT('Table'[Attribute]), FILTER( 'Table', 'Table'[Attribute] = MAX('Table'[Attribute]) && 'Table'[Value] = MAX('Table'[Value]) ) )Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi ES_TH
For your question, here is the method I provided:
First, go to the power query and Unpivot Columns for Apples and Apples-1.
Create a measure.
Measure = CALCULATE( COUNT('Table'[Attribute]), FILTER( 'Table', 'Table'[Attribute] = MAX('Table'[Attribute]) && 'Table'[Value] = MAX('Table'[Value]) ) )Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This is an excellent solution. Thank you. As I am new to this I just wanted to understand some the logic in this. I get the pivoting which is fine but it is the measure that doesn't qute make sense to me and also I am having problems calculating percenatge change as I thought you could use the "New Calculation" on visula but it doesn't seem to recognise the columns. So getting to the point and being more explicit see the logic I was trying to ascertain from visuals:
1. What is the point of the measure if I get the same output from just using the attributes in the values and doing a count
2. What is the Filter with max for attribute and value adding.
3. How do I get to do calculations on these columns? I thought the "new calculation" on visual would of helped but it doesn't seem to recognise the measure column so I can tell it to calculate percentage change for Apples and Apples-1 (I'm sure it is really simple and my syntax understanding is just missing as newbie to all of this!!!). Should I or do I even need to do it on the visual? How would I write a measure to show me this (research says that you can't format visual calculations!).
Sorry lots of questions but hope that makes sense. Will upload PBI file if i can figure out how! 😁
