PowerBi Matrix Table - Compare 2 different variables of same object

[ES_TH]

Hi,

I am using PowerBi and am trying to solve a problem of comparing the outcomes of 2 columns that are showing the same object but with 2 possible outcomes. Basically it's almost a what if scenario with column 3 being the what if... but not quite!  Anyway, I will then do some analyse on percentage changes, differences and a bunch of other stuff with extended dataset. Simplifying the main problem see below:

DATA TABLE EXAMPLE.

ID Apples Apples -1

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:

Apples Apples Apples -1

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)

Apples Apples Apples -1

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?

[Anonymous · Accepted solution]

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.

[ES_TH]

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!  😁