Drill Down Chart Related Tables Percentage

[davidqsuires]

Hello,

I have 2 tables in my model that look like this (just a sample, actual tables are much bigger): 

Table 1: 

ID	Branch	Represenative	OppStatus
1	Alabama	Tim	Open
2	Alabama	Tim	Closed
3	Alabama	Tim	Open
4	Alabama	Mark	Open
5	Alabama	Mark	Closed
6	Boston	Lauren	Open

Table 2: 

Branch	Represenative	PriorYearSales	YOYSales
Alabama	Tim	500	100
Alabama	Mark	200	50
Boston	Lauren	400	-40
Alabama	Steve	1000	200

The relationship is on Represenative

Table 2 is unique on the representaive. I am using a chart to look at metrics on Table 1, where I drill down from Branch to Represenative. I have some percentage measures built for that table. 

I then want to bring in data from Table 2 that is the Branch's growth percentage (YOYSales / PriorYearSales) at the Branch level of my chart, and the Represenative's growth percentage at the Represenative level of my chart after drilling down. 

I could do this pretty easily as seperate datasets/measures but getting it to work in 1 has been giving me issues. I thought it might be something like this code, which works at the represenative level, but does not work at the branch level: 

Growth%= 
DIVIDE(
	SUMX(DISTINCT('Table2'[Represenative]),CALCULATE(Average('Table2'[YOYSales]))),
	SUMX(DISTINCT('Table2'[Represenative]),CALCULATE(Average('Table2'[PriorYearSales])))
)

[v-yuta-msft]

Hi davidqsuires,

As a general solution. You can merge table1 and table2 with left outer join. Then create a measure using DAX formula like this:

Growth% = 
DIVIDE(
	AVERAGEX(Table, [YOYSales]),
	AVERAGEX(Table, [PriorYearSales])
)

In addtion, you said "I could do this pretty easily as seperate datasets/measures but getting it to work in 1 has been giving me issues." Could you please provide more details about the issue?

Regards,

Jimmy Tao

[davidqsuires]

Thank you for your help. Just to clarify, how do you configure a Join to be a Left Join when relating 2 tables?

This formula works for the Represenative level but doesn't work for the branch level. Other variations of the formula was also working for me, at the Represenative level but not at branch level. I think it might have something to do with what I said last, not all represenatives are in table 1.

I think I need to relate Table 1 to Table 2 on Represenative, but then create a Table 3 that's aggregated to the Branch level, and relate Table 3 back to 1 on Branch. Then create a measure that says if I am at the Branch level in my chart, use the Table 3 relationship/percentage, if I am the Represenative level in my chart use the Table 2 relationship/percentage. 

I wish i could attach a test/sample PBI with hand entered data, is that possible?

[davidqsuires]

I added 1 extra row ("Steve") in table 2 that better illustrates my problem. When doing the branch level, i need it to ignore the represnative relationship. I am not sure there is a way to do this. The reason i said this could be done easily if it was 2 seperate measures is because in my case, i need it to be 1 measure. See my 2 example pictures where I start with Branch and drill down to Represenative. I don't want to setup 2 seperate growth% measures where 1 won't make sense at the various levels. Unless i could dynamically hide one or the other. 

You can see based on the data, the growth % should be 20.6 not 21.4 for Alabama. It's not including the Steve row. 

[davidqsuires · Accepted solution]

I got this to work by searching other posts. I had to create seperate datasets and relationships for the Represenative & Branch. Then a Dynamic measure using HASONEVALUE that will use a different relationship, depending at what level the report is at:

GrowthHasOne% =
IF (
    HASONEVALUE ( Opps[Represenative] ),
    DIVIDE ( SUM ( 'Totals'[YOYSales] ), SUM ( 'Totals'[PriorYearSales] ), 0 ),
    DIVIDE (
        SUM ( TotalsBranch[YOYSales] ),
        SUM ( TotalsBranch[PriorYearSales] ),
        0
    )
)

[davidqsuires]

Also to mention it, there are sometimes extra "Represenatives" in Table 2, that are not in Table 1. But I would like the percentage at the Branch level to reflective of all the Represenatives in table 2.