Forum Discussion
Clustered Bar Chart with Unpivoted Data - Combining Two Types of Data
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] ) )
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 )@AlexisOlson. Thanks for your help on this. That solution did work in my simple example where I had a dim table that wasn't linked to the rest of my data, but now I need to make it more complicated. I'm actually already using a dim table with my agencies, and I've linked that dim table to my "LeadAgencies" Table by the Lead Agency. When I do that, the formula for SumInvolved doesn't seem to work. Any more pointers?
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] ) )