Forum Discussion
How to summarise and transpose (?) data
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 !
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.
5 Replies
- MFelix
Super User
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.
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.
