cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ramk Frequent Visitor
Frequent Visitor

P&L Statement - GL Account Hierarchy Issues

Hi,

 

I am building a PBI report for P&L Statement to show actual, budget, variance grouped by account code which stored in a separate hierarchy table. One of the issues I am facing is how to link the Account column in either of actual or budget table with the custom GL hierarchy table. i.e the account code can be present in any of the 10 levels in the hierarchy. It has the data stored in unpivoted format (attached). Also I am not sure how the measures actual amount, budget amount, variance will be calculated when we do a rollup or drill  down from the top level hierarchies. 

 

Most of the blogs talk about first building the parent column and then calculated hierarchy depth using PATH functions in DAX. For me, it is a slightly different  case and need to unpivot the data so that every account  as its immediate parent in 2 columns so that I can continue to use HierarchyDepth and use them in the calculation of the measure.

 

Or am I missing something very simple as I already have the flattended structure needed ?  Please let me know.

 

 

Hierarchy.png

 

Hierarchy sample data.png

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: P&L Statement - GL Account Hierarchy Issues

@ramk,

 

You may try using SELECTCOLUMNS Function (DAX) and UNION Function to add a calculated table.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: P&L Statement - GL Account Hierarchy Issues

@ramk,

 

You may try using SELECTCOLUMNS Function (DAX) and UNION Function to add a calculated table.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Panda2018 Member
Member

Re: P&L Statement - GL Account Hierarchy Issues

Hi,

 

Did you able to do the Actual & Budget report?

Can you please share with me the PBI report with me? 

I will really appreciate it.

 

My email  spanda@unitycare.org

 

Thanks,

panda2018

Panda2018 Member
Member

Re: P&L Statement - GL Account Hierarchy Issues

Hi,

 

Did you able to do the Actual & Budget report?

Can you please share with me the PBI report with me? 

I will really appreciate it.

 

My email  spanda@unitycare.org

 

Thanks,

panda2018