Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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,
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
Solved! Go to Solution.
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.
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.
Thanks for detail replies
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
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
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
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 , Based on what I got
There are few solutions discussed on this issue, see if those can help
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
20 | |
17 | |
17 | |
11 | |
7 |
User | Count |
---|---|
27 | |
27 | |
13 | |
12 | |
12 |