cancel
Showing results for
Did you mean:
Helper III

## Show the Item in visualization which not exist in Dataset

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?

1 ACCEPTED SOLUTION
Community Support

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

2 REPLIES 2
Community Support

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

Helper III

Anyone know how to handle this case?

Announcements