Custom Matrix Table Power BI based on measures

[conniedevina]

Hi All,

I'm having little bit problem, so I want to create custom matrix table for KPI and hoping it would look like this 

So for Budget I have this measure to calling each measures

Budget = var a = SELECTEDVALUE('KPI Report'[ID])
return
SWITCH(TRUE(),
a=11,[Business_day BUD Lunch],
a=12,[Business_day BUD Cafe],
a=13,[Business_day BUD Dinner],
a=21,'Measure List'[Total_Budget Amount Lunch],
a=22, 'Measure List'[Total_Budget Amount Cafe],
a=23, 'Measure List'[Total_Budget Amount Dinner])

and below for actual

Actual = var a = SELECTEDVALUE('KPI Report'[ID])
return
SWITCH(TRUE(),
a=11,[Business_day BUD Lunch],
a=12,[Business_day BUD Cafe],
a=13,[Business_day BUD Dinner],
a=21,[Total_Actual Amount Lunch],
a=22, [Total_Actual Amount Cafe],
a=23, [Total_Actual Amount Dinner])

So it will have measure for each line of the rows because actually for the KPI I created custom table to make the matrix table like that.

and here is the KPI table that I've created

The problem is I'm not sure why the total is blank like below

Is it because I'm using Switch as my final measure so it won't sum the total?

I attach the pbix file here with the sample data.

Any help will be very appreciated! Thank you!

[Anonymous · Accepted solution]

Hi @conniedevina ,

I updated your sample pbix file(see attachment), please check whether that is what you want. You can create another new two measures as below and put them on the matrix to replact the original measures [Budget] and [Actual]:

nBudget = 
SUMX (
    GROUPBY ( 'KPI Report', 'KPI Report'[KPI], 'KPI Report'[Shift Order] ),
    [Budget]
)

nActual = 
SUMX (
    GROUPBY ( 'KPI Report', 'KPI Report'[KPI], 'KPI Report'[Shift Order] ),
    [Actual]
)

Best Regards