Forum Discussion
Clustered Bar Chart with Unpivoted Data - Combining Two Types of Data
Hello
I have a single table (See "Activities by lead agency") that has information about which agency is the lead and any other agencies that are involved. In order to make the second green graph I unpivoted the agency columns. Both of the tables are in the data model and they're linked via the ID column. My question is how can I combine these two graphs so that, the PQR agency - for example - would have a green bar chart showing that it is involved in 8 projects and a blue bar indicating that it's leading 4 projects.
Keep the unpivoted table (you can get rid of the other one) and create a relationship from Agency to LeadAgency and another (inactive) relationship from Agency to InvolvedAgency.
The CountLead measure stays the same but the SumInvolved has to activate the inactive relationship:
SumInvolved = VAR CurrAgency = VALUES ( dimAgency[Agency] ) RETURN CALCULATE ( SUM ( Unpivoted[Value] ), Unpivoted[Involved Agency] IN CurrAgency, USERELATIONSHIP ( Unpivoted[Involved Agency], DimAgency[Agency] ) )
7 Replies
You should be able to use the unpivoted version for both. Distinct count of Local Agency for the blue bars and sum of Value for the green bars.
AlexisOlson . That doesn't seem to have worked. When I throw in the distinct count of Lead Agency from the Unpivoted data, it's showing me that PQR is in 3 times and DEF is in 4. PQR should be in 4 times and DEF should be in twice. Any other tips?
You're right. It isn't that simple. The problem is that whichever agency column you for the axis will make things difficult to calculate for the other one.
I'd recommend creating a dimension table with one row for each agency that appears in either column and then using that table's column for the x-axis. Then you can define both measures using the dimension table for filter context.
For example:
CountLead = VAR CurrAgency = VALUES ( dimAgency[Agency] ) RETURN CALCULATE ( DISTINCTCOUNT ( Unpivoted[ID] ), Unpivoted[Lead Agency] IN CurrAgency ) SumInvolved = VAR CurrAgency = VALUES ( dimAgency[Agency] ) RETURN CALCULATE ( SUM ( Unpivoted[Value] ), Unpivoted[Involved Agency] IN CurrAgency )
