How to summarise and transpose (?) data

[SteffanH]

I have the following (simplified) data

Column	Current Value	Previous value
CL1	Transitioning	Leading
CL2	Leading	Winding Down
CL3	Considering	Promising
CL5	Transitioning	Promising
CL4	Promising	Launching

I am looking for the best way to create a 'summary' of this data in the following form

	Current value	Previous Value
Launching	0	1
Considering	1	0
Leading	1	1
Promising	1	2
Transitioning	2	0
Winding Down	0	1

(the numbers are a count of the occurence of each value in 'previous' or 'current')

In order to be able to (ideally) provide the following visualisation (excel mockup)
 

I have the feeling that a Calculated table might be the correct approach, but I have so far been able to find an example that comes close to what I am trying to achieve.

Any pointers in the right direction would be highly appreciated !

[MFelix · Accepted solution]

Hi @SteffanH ,

Believe there is no need to create a new sumarization table. Try the following steps:

• Add a new table with the status (without any relationship to the other table)
• Create the following two measure:

Current = CALCULATE(COUNT('Values'[Column]);FILTER('Values';'Values'[Current Value]= SELECTEDVALUE('Status'[Status]))) + 0

Previous = CALCULATE(COUNT('Values'[Column]);FILTER('Values';'Values'[Previous value]= SELECTEDVALUE('Status'[Status]))) + 0

• Then  use this on your two visualizations

Other option to go is:

• Unpivot the Current Value and Previous value columns
• Create a status table with relationship active to the first table
• Create this two measures:

CurrentUnpivot = CALCULATE(COUNT('Values (Unpivot)'[Column]);'Values (Unpivot)'[Type] = "Current value") + 0

PreviousUnpivot = CALCULATE(COUNT('Values (Unpivot)'[Column]);'Values (Unpivot)'[Type] = "Previous value") + 0

• Create your visualizations normally.

Check PBIX file attach.

[SteffanH]

Hi Miguel,

Thank you for your response !

I am unable to open the attached pbix file with PBI v2.76 (December 2019) and get a syntax error on your suggested measure after 

CALCULATE(COUNT('Values'[Column]);

For anyone else trying to reproduce: I managed to resolve this by changing the ";" after COUNT to a "," and the same applies for the ";" after FILTER

This is exactly what I needed 👍

But I have a follow-up question:
When I click on any of the summarised values, a table I added to the same report page does not get filtered, by the selected Status is that because no link exists between the newly created "Status" helper table ? 

[MFelix]

Hi @SteffanH 

Regarding the question about the dot comma and comma is regional settings related, depending on the settings of your computer the function parameters are determined by dot comma or comma. 😀

If you went to the first option that is a non-related table that is correct, since you don't have a relationship you will not get filtering information.

[SteffanH]

Was afraid of the regional settings thing being the cause.

Filtering should only be needed/applied on the "current" value, I assume the existing measures will not break if I create the relation between Current value and the new Status table.

[MFelix]

Hi @SteffanH ,

You can do two relationship one active on current value and one inactive on previous then change your measures to:

Current = COUNT('Values'[Column])

Previous = CALCULATE(COUNT('Values'[Column]);USERELATIONSHIP('Values'[Previous value]; Status{[Status])) + 0

 Should work as expected and then the filtering will be applied.