Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi There,
When the data i use is exported it loses the hierarchy that is set in the database and I am trying to reinstate that so that the data flows up and down properly. An example of what I have is below.
Hierarchy Group Order Number Cost Revenue
**** Header Group 123456 1000 2000
*** Sub Group 1 123456-01 600 1200
** Individual Order 1 123456-01-01 450 850
** Individual Order 2 123456-01-02 150 350
*** Sub Group 2 123456-02 400 800
** Individual Order 1 123456-02-01 350 700
** Individual Order 2 123456-02-02 50 100
Because of the way it is set up when i visualise it I get duplicated values since it pulls both the value for the Sub group and its individual orders, rather than having the individual orders as additonal data points. Is there any way I can transform it to actually gain back the heirarchy?
My End goal is to be able to have a Matrix where I can have:
Cost Revenue
Header group 1000 2000
-> Sub Group 1 600 1200
-> Individual Order 1 450 850
-> Individual Order 2 150 350
etc...
Any help would be great.
Solved! Go to Solution.
Hi @rhayward2209 ,
Based on your description, you can create a calculated table as follows.
Test =
var x1=SUMMARIZE(FILTER('Table',LEFT([Group],3)="Ind"),[Group],'Table'[Order Number],'Table'[Cost],'Table'[Revenue])
return
ADDCOLUMNS(x1,"Sub",IF(LEFT(RIGHT([Order Number],5),2)="01","Sub Group 1","Sub Group 2"),"Head","Header Group")
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rhayward2209 ,
Based on your description, you can create a calculated table as follows.
Test =
var x1=SUMMARIZE(FILTER('Table',LEFT([Group],3)="Ind"),[Group],'Table'[Order Number],'Table'[Cost],'Table'[Revenue])
return
ADDCOLUMNS(x1,"Sub",IF(LEFT(RIGHT([Order Number],5),2)="01","Sub Group 1","Sub Group 2"),"Head","Header Group")
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Is my best bet trying to create another data set that works as an index for how the hierarchy and relationships work for this data?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
74 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |