P&L Statement - GL Account Hierarchy Issues

[ramk]

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.

[Anonymous]

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

[Anonymous]

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

[v-chuncz-msft · Accepted solution]

@ramk,

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