Hey, Hope all of you are good. Please find below data, remarkreportdatelobgeographyassetclasssublobValue(int)lob13-Dec-21Markets 1lgas13-Dec-21MarketsDomesticEquityA2lgas13-Dec-21MarketsDomesticEquityB3lgas13-Dec-21MarketsDomesticEquityC4lgas13-Dec-21MarketsDomesticEquityD5lgas13-Dec-21MarketsDomesticEquityE6lgas13-Dec-21MarketsDomesticEquityH7lgas13-Dec-21MarketsDomesticEquityF8lgas13-Dec-21MarketsDomesticEquityG9lgas13-Dec-21MarketsInternationalEquityB1lgas13-Dec-21MarketsDomesticCurrencyF2lgas13-Dec-21MarketsDomesticCurrencyD3lgas13-Dec-21MarketsInternationalCurrencyD4lgas13-Dec-21MarketsInternationalEquityD5lga13-Dec-21MarketsDomesticCurrency 6lga13-Dec-21MarketsDomesticEquity 7lga13-Dec-21MarketsInternationalEquity 8lga13-Dec-21MarketsInternationalCurrency 9lg13-Dec-21MarketsInternational 1lg13-Dec-21MarketsDomestic 2 ====>>>>Wrong Output coming,hierarchy of matrix shown below, 1) 2) 3) 4) I need output in hierarchy wise below, Kindly help asap. 1) 2) 3) 4) Please Help MVP of Power Bi.....Super heroes of power bi amitchandak AlexisOlson v-zhangti Anand24 v-yalanwu-msft

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: 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.

Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Urgent help- Dax

Hey, Hope all of you are good.

Please find below data,

remark	reportdate	lob	geography	assetclass	sublob	Value(int)
lob	13-Dec-21	Markets				1
lgas	13-Dec-21	Markets	Domestic	Equity	A	2
lgas	13-Dec-21	Markets	Domestic	Equity	B	3
lgas	13-Dec-21	Markets	Domestic	Equity	C	4
lgas	13-Dec-21	Markets	Domestic	Equity	D	5
lgas	13-Dec-21	Markets	Domestic	Equity	E	6
lgas	13-Dec-21	Markets	Domestic	Equity	H	7
lgas	13-Dec-21	Markets	Domestic	Equity	F	8
lgas	13-Dec-21	Markets	Domestic	Equity	G	9
lgas	13-Dec-21	Markets	International	Equity	B	1
lgas	13-Dec-21	Markets	Domestic	Currency	F	2
lgas	13-Dec-21	Markets	Domestic	Currency	D	3
lgas	13-Dec-21	Markets	International	Currency	D	4
lgas	13-Dec-21	Markets	International	Equity	D	5
lga	13-Dec-21	Markets	Domestic	Currency		6
lga	13-Dec-21	Markets	Domestic	Equity		7
lga	13-Dec-21	Markets	International	Equity		8
lga	13-Dec-21	Markets	International	Currency		9
lg	13-Dec-21	Markets	International			1
lg	13-Dec-21	Markets	Domestic			2

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

hierarchy of matrix shown below,

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

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

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:

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.

W41_Urgent help- Dax.pbix44 KB

7 Replies

amitchandak
Super User
4 years ago
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
Samarth_18
Community Champion
4 years ago
Hi Anonymous ,
To achive this, you can follow below steps:-
1. Right click on lob column and select create a hierarchy:-
2. Now you will see new hierarchy created

3. Now right click on geograph column and click on add to hierarchy and add it lob hierarchy:-
4.Follow the same step for asset class and sub lob. You will see below output
5. Now add this hierachy into the matrix and put filter for non blank value in assetclass and sublob column
Thanks,
Samarth
- Anonymous
  Not applicable
  4 years ago
  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 .
Anonymous
Not applicable
4 years ago
No solution with you? Power bi Superheroes????? 😐
- smpa01
  Community Champion
  4 years ago
  Anonymous
  
  How-to-Get-Your-Question-Answered-Quickly
  
  How-to-provide-sample-data-in-the-Power-BI-Forum

v-angzheng-msft

Community Support

4 years ago

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:

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.

W41_Urgent help- Dax.pbix44 KB

Anonymous
Not applicable
4 years ago
Thanks for detail replies

Forum Discussion

Urgent help- Dax

7 Replies

Related content

DAX Formula Help

DAX Query help

DAX Help

Need Urgent Help on SamePeriodLastYear

urgent help needed