Adding Actual and Target rows to the same column with Hybrid Column Matrix

[JB505]

I'm trying to create a hybrid matrix and running into a problem when it comes to actual and target values. I want them shown in the same column on two different rows. Essentially, I'm trying to create a bowler report in power bi.

Similar to the look of this. Where I can add conditional formatting to each cell for actual/target.

Here is where I'm at with my hybrid matrix.

Here is what I'd like it to look like, with actual/target in same column. Where rolled up the kpi would show an average of each of the corp_codes and rolled down, it shows each individual location.

Here are screen shots of the sample data.

Model

Test Table

Hybrid Table

EDIT: Added link to the file.

KPI_Test.pbix 

EDIT: Added an additional work around for this in a reply below, but would still need a solution for it.

Hope this is possible. Any help is appreciated.

[JB505]

Another option that might work is this. However, is there a way to remove actual/target/py from KPI Level, Status and Owner? Scenario is just a table with actual/target/py. If anyone knows how to limit secondary column values to certain primary columns in a hybrid matrix please let me know.

[bolfri]

Can you use a https://wetransfer.com/ to give us you PBI file with sample data that you put in this post or sample data in version that we can copy and pase (not image)? 🙂

[JB505]

Hope this works. Let me know if you are able to open the pbix file.

https://we.tl/t-g0pEWzOK0l

[bolfri]

Can you tell me why do you want to create that hybrid table? Woudn't it be easier for you to use Matrix visual and addapt it to your needs? Like this:

Actual and Target are meaures:

Actual = CALCULATE(AVERAGE(TEST[periodic]),TEST[scenario]="Actual")

Target = CALCULATE(AVERAGE(TEST[periodic]),TEST[scenario]="Target")

and the other columns comes from TEST table except period_name that comes from Dim_Calendar.

[JB505]

Hi Bolfri,

Thanks for trying to assist with this request.

The reason for the hybrid table is because additional columns are needed.

KPI Level - As each layer is expanded, we need to know what kpi level that row sits at.

Status - This column is just going to be a conditional cell that shows Green if we are at/above the target based on all months showing.

Owner - This column is going to show who is responsible for that "Row" or layer of data.

Scenario - This was to show Actual and Target.

If I didn't use a hybrid table, how else could I include the above columns without adding them to the row field? Maybe there's an easier solution that I'm just not seeing.

[bolfri]

Let's focus on one of them. How are you defining a owner? Do you have a eg. table with string of name with assigned corp_code? If yes - then you can just simple include that in the table without a problem. 

[JB505]

So using owner as an example. Yes there will be a table with owners being related to corp_code. However, there shouldn't be another drilldown to see who that owner is. Drilldowns should only happen when there is a new kpi level. So when the table is rolled up all the way that's level 1, when the next drilldown happens that shows corp_code, that's level 2. If we continue adding those columns to the row field then it will continue adding another layer of drilldown that shouldn't happen.

[JB505]

So here's an updated example of what it would look like.

Rolled up you see the kpi with the level, status, owner, and months

when rolled down a level, you see how everything shows without having to continue rolling down a level. There should only be a level 1 and level 2.

Everything else seems to be working, it's just the column that should show actual/target that I can't get to work...