Show the Item in visualization which not exist in Dataset

[KH_Mike]

Hi All,

I connect to the PBI Dataset which contian the data like below. The Sub Group for Group A & B contain "Total" which equal to calculated in back end. However, for Group C, the dataset only contain Sub Goup "AA" & BB". 

Group	Sub Group	Amount
A	AA	10
A	BB	20
A	Total	25
B	AA	20
B	BB	30
B	Total	35
C	AA	10
C	BB	10

Now, I want to show the table like below

Group	Amount	Explain
A	25	Sum the Group "A" & Sub Group "Total"
B	35	Sum the Group "B" & Sub Group "Total"
C	XX	Sum the Group "C" & Sub Group "AA" & Sum the Group "C" & Sub Group "BB" * 0.8

Is it possible to acheive it DAX formula instead of amendming the Dataset?

[v-yangliu-msft · Accepted solution]

Hi  @KH_Mike ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
SUMMARIZE('Table','Table'[Group],
"Amount",
SWITCH(
TRUE(),
'Table'[Group]="A",CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Group]="A"&&'Table'[Sub Group]="Total")),
'Table'[Group]="B",CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Group]="B"&&'Table'[Sub Group]="Total")),
'Table'[Group]="C",CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Group]="C"&&'Table'[Sub Group] in {"AA","BB"}))*0.8))

2. Result:

Best Regards,

Liu Yang

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

[KH_Mike]

Anyone know how to handle this case?