cancel
Showing results for
Did you mean: Frequent Visitor

## Hierarchal values without drill

input :

impact,  L1_Country ,L2_State  ,L3_City

 100 IND 40 IND KA 35 IND TN 25 IND MH 10 IND KA BLR 20 IND KA HSR 10 IND KA KLR

Output: is this possible ?

1 ACCEPTED SOLUTION Frequent Visitor

Thanks for your response. this idea worked for me.  I made few changes to your formula to restrict blanks at each level.

values = VAR L1= CALCULATE (sum(main[impact]),ISBLANK(main[L2_State])=TRUE)
VAR L2= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L2_State]),ISBLANK(main[L3_City])=TRUE)
VAR L3= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L3_City]))
RETURN L1+L2+L3
2 REPLIES 2  Super User

@jonnalav , Create a new table

Dim = filter(distinct(union(distinct(Table[L1_Country]), distinct(Table[L2_State]), distinct(Table[L3_City]))),[L1_Country] <> blank())

Join this table (only column- L1_Country) with all three columns

Assume L1_Country to L1_Country is active and others are inactive

Then create a meausre like this and use

sum(Table[impact]) + calculate(sum(Table[impact]), userelationship(dim[L1_Country], Table[L2_State]))

+ calculate(sum(Table[impact]), userelationship(dim[L1_Country], Table[L3_City]))

refer if needed

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User! Frequent Visitor

Thanks for your response. this idea worked for me.  I made few changes to your formula to restrict blanks at each level.

values = VAR L1= CALCULATE (sum(main[impact]),ISBLANK(main[L2_State])=TRUE)
VAR L2= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L2_State]),ISBLANK(main[L3_City])=TRUE)
VAR L3= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L3_City]))
RETURN L1+L2+L3  