Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Urgent help- Dax

Hey, Hope all of you are good.

 

Please find below data,

 

remarkreportdatelobgeographyassetclasssublobValue(int)
lob13-Dec-21Markets   1
lgas13-Dec-21MarketsDomesticEquityA2
lgas13-Dec-21MarketsDomesticEquityB3
lgas13-Dec-21MarketsDomesticEquityC4
lgas13-Dec-21MarketsDomesticEquityD5
lgas13-Dec-21MarketsDomesticEquityE6
lgas13-Dec-21MarketsDomesticEquityH7
lgas13-Dec-21MarketsDomesticEquityF8
lgas13-Dec-21MarketsDomesticEquityG9
lgas13-Dec-21MarketsInternationalEquityB1
lgas13-Dec-21MarketsDomesticCurrencyF2
lgas13-Dec-21MarketsDomesticCurrencyD3
lgas13-Dec-21MarketsInternationalCurrencyD4
lgas13-Dec-21MarketsInternationalEquityD5
lga13-Dec-21MarketsDomesticCurrency 6
lga13-Dec-21MarketsDomesticEquity 7
lga13-Dec-21MarketsInternationalEquity 8
lga13-Dec-21MarketsInternationalCurrency 9
lg13-Dec-21MarketsInternational  1
lg13-Dec-21MarketsDomestic  2

 

 

====>>>>Wrong Output coming,

hierarchy of matrix shown below,

 

SankeyThakkar_7_1-1639472455651.png

 

1)

SankeyThakkar_7_0-1639472332825.png

 

2)

SankeyThakkar_7_2-1639472556813.png

 

3)

 

SankeyThakkar_7_3-1639472598428.png

 

4)

 

SankeyThakkar_7_4-1639472652999.png

 

SankeyThakkar_7_5-1639472671405.png

 

 

 

I need output in hierarchy wise below, Kindly help asap.

 

1)

 

SankeyThakkar_7_6-1639472851072.png

 

2)

 

SankeyThakkar_7_7-1639472951862.png

3)

 

SankeyThakkar_7_8-1639473723352.png

 

4)

SankeyThakkar_7_9-1639473965167.png

 

 

Please Help MVP of Power Bi.....Super heroes of power bi

 

@amitchandak 

@AlexisOlson 

@v-zhangti 

@Anand24 

@v-yalanwu-msft 

 

 

 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

Try to create a measure like this:

Measure = 
var _t1=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK())
var _t2=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK())
var _t3=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK()&&'Table'[geography]=BLANK())
var _t4=0  //whatever you want
// CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK()&&'Table'[geography]=BLANK()&&'Table'[lob]=BLANK())

var _if=IF(
    ISINSCOPE( 'Table'[lob] ),
    IF(
        ISINSCOPE( 'Table'[geography] ),
        IF(
            ISINSCOPE( 'Table'[assetclass] ),
            IF( 
                ISINSCOPE( 'Table'[sublob] ), 
                SUM('Table'[Value(int)])// 0
                , 
                _t1// 1 
                ),
            _t2// 2
        ),
        _t3// 3
    ),
    _t4// 4
)
return _if


Result:

vangzhengmsft_0-1640840711673.png

Please refer to the attachment below for details. Hope this helps.

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

View solution in original post

7 REPLIES 7
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

Try to create a measure like this:

Measure = 
var _t1=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK())
var _t2=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK())
var _t3=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK()&&'Table'[geography]=BLANK())
var _t4=0  //whatever you want
// CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK()&&'Table'[geography]=BLANK()&&'Table'[lob]=BLANK())

var _if=IF(
    ISINSCOPE( 'Table'[lob] ),
    IF(
        ISINSCOPE( 'Table'[geography] ),
        IF(
            ISINSCOPE( 'Table'[assetclass] ),
            IF( 
                ISINSCOPE( 'Table'[sublob] ), 
                SUM('Table'[Value(int)])// 0
                , 
                _t1// 1 
                ),
            _t2// 2
        ),
        _t3// 3
    ),
    _t4// 4
)
return _if


Result:

vangzhengmsft_0-1640840711673.png

Please refer to the attachment below for details. Hope this helps.

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

Anonymous
Not applicable

Thanks for detail replies

 

Anonymous
Not applicable

No solution with you? Power bi Superheroes????? 😐

@Anonymous 

 

How-to-Get-Your-Question-Answered-Quickly 

 

How-to-provide-sample-data-in-the-Power-BI-Forum 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Samarth_18
Community Champion
Community Champion

Hi @Anonymous ,

To achive this, you can follow below steps:-

1. Right click on lob column and select create a hierarchy:-

Samarth_18_0-1639475699793.png

2. Now you will see new hierarchy created

Samarth_18_1-1639475763546.png

 

3. Now right click on geograph column and click on add to hierarchy and add it lob hierarchy:-

Samarth_18_2-1639475889964.png

4.Follow the same step for asset class and sub lob. You will see below output

Samarth_18_3-1639475971727.png

5. Now add this hierachy into the matrix and put filter for non blank value in assetclass and sublob column

Samarth_18_4-1639476281616.png

Thanks,

Samarth

 

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Anonymous
Not applicable

Thanks, For your valuable time. Actually if you see my desire output . I required value as it is in total. In your solution I got value of category as subtotal(sum). Which is not correct solution. Kindly help. Not getting desired output from 5 day. @Samarth_18  and @amitchandak .

amitchandak
Super User
Super User

@Anonymous , Based on what I got

There are few solutions discussed on this issue, see if those can help 

https://community.powerbi.com/t5/Custom-Visuals-Development/Remove-empty-hirearchy-levels/m-p/1091582

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.