Adding a new row to matrix

[leila675]

Hi there 

I am hoping someone can help me.  I have spent far too many hours trying to figure this out.  I am relatively new to Dax but quite familiar with power query and pbi.  That said I have a matrix built with measures that compare two values in two separate tables.  I basically want to be able to take the difference calculated between the two and add it as a row in my matrix to make the two reconcile.  group other or Group misc something like that.  Because a measure can't be a row I am not sure how to accomplish that and I have tried many things and watched many videos.  I thought the win was going to be a disconnected table but  where I get tripped up is all the groupings/filters in my other days don't apply obviously because it's not a connected table.  Here is a sample of what I have in Table 1 and what I am hoping to get to in Table 2. 

Appreciate anybody who can help so I can get this off my plate 😀

Fyi I know a few ways I could maybe do it in PQ but alot of my added columns of data  have been done in DAX already so the fields I need don't appear on PQ when trying to edit there.

 

[v-nmadadi-msft]

Hi @leila675 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @leila675 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @leila675,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rajendraongole1 · Accepted solution]

Hi @leila675  - Create a disconnected table for row labels

GroupList =
DATATABLE("Group", STRING, {
{"Group A"},
{"Group B"},
{"Group C"},
{"Variance"}
})

create a measure for totals to return Total2

NewTotal =
VAR SelectedGroup = SELECTEDVALUE(GroupList[Group])
RETURN
SWITCH(
TRUE(),
SelectedGroup = "Group A", CALCULATE(SUM(Table2[Value]), Table2[Group] = "Group A"),
SelectedGroup = "Group B", CALCULATE(SUM(Table2[Value]), Table2[Group] = "Group B"),
SelectedGroup = "Group C", CALCULATE(SUM(Table2[Value]), Table2[Group] = "Group C"),
SelectedGroup = "Variance",
CALCULATE(SUM(Table1[Value])) - CALCULATE(SUM(Table2[Value])),
BLANK()
)

now place new total at values , Power BI will auto-calculate the Grand Total row (i.e., 47). Hope this works.