Value fetching in hierarchy type data

[Anonymous]

Dear Super Heroes,

Below is my sample data.

I need Output like below,

I created below measure, Which is working only when level 4 is there. If level 4 is not exist it will not showing my remaining values,

Below is my measure,

Measure =

IF(

MAX('Table'[Level4]) <> "",

IF(

ISINSCOPE('Table'[Level4]),

CALCULATE(SUM('Table'[Value])),

IF(

ISINSCOPE('Table'[Level3]),

CALCULATE(SUM('Table'[Value]),'Table'[Level4]=""),

IF(

ISINSCOPE('Table'[Level2]),

CALCULATE(SUM('Table'[Value]),'Table'[Level3]=""),

IF(

ISINSCOPE('Table'[Level1]),

CALCULATE(SUM('Table'[Value]),'Table'[Level2]=""),

BLANK()

)))))

After using measure below is my output coming,

Kindly help please.

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. According to the selected column – Transform – Unpivot Columns.

Result：

2. Create measure.

Measure =
var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev3")),_a)

Measure 3 =
var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev1")),_a)

Measure 4 = var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev2")),_a)

Measure 5 = var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev4")),_a)

For reference =
calculate(MAX('Table'[Attribute]),FILTER('Table',NOT(ISBLANK([Value.1]))))

3. Result:

Please click here for the pbix file

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

[Anonymous]

Thanks for help. I loose hope as no one is replying.

This solution has 2 major issues sir,

(1) Need to show in matrix so heirarchy is required.(ig level 4 iteam J is coming under what level of 1 ,2 and 3.
(2) If i applied this solution it increases row size very much.(if i have 500 rows as normal data set it will become 4500) So my actual data size is too much.

[Anonymous]

@tamerj1  @amitchandak @Jihwan_Kim  @Whitewater100 @johnt75  Kindly help heroes

[Anonymous]

No solution?