Combining Multiple Data 'Views' on Single Chart

[ahiemstra]

Hello,

I have an idea and I’m wondering whether it is possible in Power BI.

I have a dataset which I have used to create several different reports. The reports mainly display column charts with data organized in different ways. I’m wondering if it’s possible to ‘combine’ elements from each of the charts and have them display on 1 chart. I’ll try to explain using visuals.

On this chart, you can see I’m showing Average Encounters/Day by Provider:

Of note, see that Kyla B’s Average Encounters/Day is 3.6. Also note, the highest Average/Day is Tiffany W at 5.3.

On this next chart, you can see Average Encounters/Day by Discipline:

Of note, see that the Average Encounters/Day for RDs is 3.8 (all providers shown in the chart above are part of the RD category)

Ideally, what I would like to do is create a chart which shows Kyla’s average (3.6), next to the Discipline (RD) average (3.8), next to the RD with the highest average which is Tiffany W (5.3). Something like this:

(this example was mocked up in Excel)

Is something like this possble in Power BI? I can share a PBIX file if it would be helpful but before going to the extent of doing so I wanted to first ask if the concept I have in mind is doable or not in Power BI.

[Ritaf1983 · Accepted solution]

Hi @ahiemstra 
Yiu need ti calculate 3 different measures with the needed aggregations and "filter manipulations" and put them on the wanted graph.
Something like :

There are 3 "independent measures" on the graph.

I attached my pbix with the example

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @ahiemstra 

Thanks for the reply from Ritaf1983 .

So far I have modified your measure with the following results:

Avg Encounters/Day Discipline = 
VAR _count1 = CALCULATE(COUNT('1 ON 1'[Encounter Time]), FILTER('1 ON 1', [Activity Description] = SELECTEDVALUE('1 ON 1'[Activity Description])))
VAR _count2 = COUNTX ( SUMMARIZE ( '1 ON 1', '1 ON 1'[Employee ID], '1 ON 1'[Encounter Date]  ), '1 ON 1'[Encounter Date])
RETURN
DIVIDE(_count1, _count2)

Avg Encounters/Day Provider = 
VAR _count1 = CALCULATE(COUNT('1 ON 1'[Encounter Time]), FILTER('1 ON 1', [Employee Full Name] = SELECTEDVALUE('1 ON 1'[Employee Full Name])))
VAR _count2 = COUNTX (
    SUMMARIZE ( '1 ON 1', '1 ON 1'[Employee ID], '1 ON 1'[Encounter Date]  ),
    '1 ON 1'[Encounter Date]
)
RETURN
DIVIDE(_count1, _count2)

Output:

However, I have noticed a problem in that the results are the same for both measures. I'm not sure if this is a result of incomplete example data? Please feel free to let me know if there are any other problems.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ahiemstra]

I'm attaching a PBIX file and hoping someone might be able to assist. It's a variation of my earlier description, but follows a similar approach.

I've created two separate measures: Avg Encounters/Day Discipline and Avg Encounters/Day Provider. The calculation for both measures is identical, but for the "Discipline" measure, I want the result to be based on the selection in the 'Activity Description' slicer. For the "Provider" measure, I want the result to reflect the selection in the 'Provider' slicer.

I’ve been researching how to make this work but haven’t had much success. I came across the SELECTEDVALUE function, which I tried implementing, but it’s leading to errors, as you’ll see in the file.

Ultimately, I’m aiming to create a chart (either line or column) that displays the average for the Provider alongside their Discipline average over time.

https://ln5.sync.com/dl/6eafbd230#mt2abezm-px98hehc-d5a4pvsi-dtkjvcu3

[Ritaf1983 · Accepted solution]

Hi @ahiemstra 
Yiu need ti calculate 3 different measures with the needed aggregations and "filter manipulations" and put them on the wanted graph.
Something like :

There are 3 "independent measures" on the graph.

I attached my pbix with the example

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.