Parent child aggregation DAX code not working

[amarsale85]

Hi Experts,

I followed the link below to create my DAX code measure:

https://community.powerbi.com/t5/Desktop/Summing-Aggregating-on-Parent-Child-Hierarchies/m-p/394500#...

In the example given in link, the code will only work with "Company" attribute but if i add attributes from another dimensions say Location, SalesType, etc., this DAX code won't work. So what change i need to do in the DAX code for it work with attributes in dimensions?

Please help.

[mwegener]

Hi @amarsale85 ,

can you provide some sample data?
Are you using your star scheme?

[amarsale85]

In the 1st post i had given all details of data which got lost as i got some error, same happened 2nd time. 3rd time i kept it short 

Anyways here is my data,

DimLocation

LocationID	Location	Parent Location ID
1	North America	NULL
2	USA	1
3	Florida	2
4	Miami	3
5	Fort Myers	3
6	Canada	1
7	Ontario	6
8	Toronto	7

DimAmountType

AmountTypeID	Amount Type
1	Registration Fees
2	License Fees
3	Product Cost

FactAmount

FactKey	LocationID	AmountTypeID	Amount
1	1	3	1000
2	2	1	250
3	4	2	150
4	5	2	100
5	6	3	1500
6	7	3	1200
7	8	1	300
8	8	2	200

[amitchandak]

@amarsale85 , refer if these can help

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi...

[amarsale85]

Thank you for the help but unfortunately this doesn't help me.

I should be able to show data in chart as shown below:

 Here you can see, All Locations selected are from different levels, but are seen besides each other, that is no drill down.

[Anonymous]

Hi @amarsale85 ,

First, please make sure those 3 tables have created relationships as below screen shot. Then create a stacked column chart (Axis: Location in table DimLocation, Legend: Account Type in DimAccountType and Values: Amount in table FactAmount ).

Create relationshipsCreate visual

Best Regards

Rena

[amarsale85]

Thank you for taking time to answer by question. But it isn't the expected behavior.

In your Chart, you can see North America to have lower profit amount than Canada & Ontario, which is wrong because,

State/Province = Aggregation/Summation of amount of all its Children cities

Country = Aggregation/Summation of amount of all its Children States/Provinces

Continent = Aggregation/Summation of amount of all its Children countries

[Anonymous]

Hi @amarsale85 ,

I created a sample pbix file, please check if it is what you want.

1. Create a calculated column "Parent location" to get the name of parent location in table DimLocation

Parent location =
VAR a = 'DimLocation'[Parent Location ID]
RETURN
    IF (
        ISBLANK ( 'DimLocation'[Parent Location ID] ),
        'DimLocation'[Location],
        CALCULATE (
            MAX ( 'DimLocation'[Location] ),
            FILTER ( 'DimLocation', 'DimLocation'[LocationID] = a )
        )
    )

2. Create a stacked column chart (Axis: new calculated column Parent location   Legend: Amount Type in table DimAmountType   Values: Amount in table FactAmount)

Best Regards

Rena