How to create graph with overall weighted avg as a bar along with bars based on a dimension

[Anonymous]

I have simplified this question to make it understandable. The basic format of my table is as follows.

I have comp and unit data for groups A, B, C, and D. Then I have a calculated field for Comp/Unit. (Note that my table has a lot more levels of drill down from the groups which is why the comp/unit has to be a calculated field depending on which level of roll-up is being used).

I initially created a bar chart showing actual versus budget for each group at the bottom (note that the numbers do not line up - I just created the Excel above for illustrative purposes).

However, the users also want to see a "Weighted Overall Average for ALL groups" at the top so they have a reference point. So, I created the Overall Weighted Avg for Comp/Unit as a SEPARATE bar chart above the other bar chart.

My initial solution was to have two separate bar charts with different roll-ups (one overall and one by group). However, since they are separate bar charts, their scaling auto adjusts differently. So, then I had to lock in the X-axis end point at 100 on BOTH graphs so that the "Overall" bars wouldn't extend out farther than the other chart and they would have the same scaling.

The problem is that I now want to add a drop-down for "specialty" so that people can filter these charts based on "specialty". Some specialties may have a comp per unit that is $300 and some may have a comp/unit that's $15. So, I cannot lock in the X-axis end point on both charts to $100.

However, if I do not lock in the end points, then the scaling on the "Overall" is going to be different from the "Group" which is a problem.

So, I think I need to somehow create a table that has the groups and overall in the same table so I can put them in the same graph so that the scaling will line up. Let me know if you can explain how to do this. I have created tables using "Summarize" but I don't know how to get the overall in the same table as the group dimension while also having a number of other dimensions.

Any assistance would be appreciated. If the answer requires custom programing, I probably do not have the skill set to do that.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

What is the calculation logic of "Weighted Overall Average for ALL groups"?

Is the total number of Comp divided by the total number of Unit?

If yes, we can use the following steps to meet your requirement.

1. Create a new table that contains one column. And there is no relationship between two tables.

New table = UNION(VALUES('Table'[Group]),{"Total"})

2. Create two measures like this,

Comp/Unit-Act Measure = 
var _Group = MAX('New table'[Group])
return
IF(
    _Group="Total",
    DIVIDE(SUM('Table'[Comp-Act]),SUM('Table'[Unit-Act])),
    CALCULATE(SUM('Table'[Comp/Unit-Act]),FILTER('Table','Table'[Group]=_Group)))

Comp/Unit-Bud Measure = 
var _Group = MAX('New table'[Group])
return
IF(
    _Group="Total",
    DIVIDE(SUM('Table'[Comp-Bud]),SUM('Table'[Unit-Bud])),
    CALCULATE(SUM('Table'[Comp/Unit-Bud]),FILTER('Table','Table'[Group]=_Group)))

3. Then put the New table[Group] in Axis and two measures in Values.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Anonymous]

@v-zhenbw-msft THANK YOU so very much!!!! This worked perfectly and I learned a new skill with the formula you shared. I really appreciate your help and the clear way you explained it and the PBIX file. [And yes, you understood the calculation for the weighted avg correctly.]