Compare growth for own products to competitor categories by brand

[Andwu]

Hi, I want to build a report in PBI, that shows me the following growth information in a table:

* Country

* Brand

* Growths %

* Growths % Market

My base data model looks as follows:

FactSales

* Date

* Country

* Brand

* Category

* Corporation

* Sales

* Sales Prev Year (calculated via Time Intelligence)

* Growths % (Sales / Sales Prev Year) - 1

* Growths % for my company (Calculate(Growths %, Corporation = "MyCompany"))

* Growths % competitors (this is what is not working properly)

DimDate

* Date

DimBrand

* Country

* Brand

* Brand Subcategory

* Category

The relationships as defined bidrectional, and DimBrand and FactTable are joined via a concatenated key for Country / Brand to ensure a one-to-many relationship.

The brands of my competitors are of couse not the same as my brands, I do want to compare my growth against my competitors based on the category. However, the categories are not consistently defined. Let's assume I'm a car manufacturer and I produce a sedan with the brand name 'Elite' and category Small Cars. The category of my competitors however is Medium Size Cars. This may even vary in each country. Thus I thought to create a Brand Dimension, where I define for each Brand in each Country the Competitor Category and apply this as a filter, so that my DAX formula should look similar to the following statement:

Growths % competitors = CALCULATE( Growths %, ALL (DimBrand.BrandSubcategory), FactTable[Category] = DimBrand[Category]) 

But this doesn't work.

Any ideas?

Thank you

[v-shex-msft]

Hi @Andwu,

According to your description, I build a sample , perhaps you can refer to it:

Tables:

Calculate columns:

Sales Year = SUMX(FILTER(ALL(FactSales),AND([Date].[Year]=EARLIER(FactSales[Date].[Year]),[Country]=EARLIER([Country])&&[Category]=EARLIER([Category])&&[Corporation]=EARLIER([Corporation]))),[Sales])

Sales Prev Year = SUMX(FILTER(ALL(FactSales),AND([Date].[Year]=EARLIER(FactSales[Date].[Year])-1,[Country]=EARLIER([Country])&&[Category]=EARLIER([Category])&&[Corporation]=EARLIER([Corporation]))),[Sales])

Growths % = DIVIDE(FactSales[Sales Year] , [Sales Prev Year],1) - 1

Growths % for my company = if([Corporation]="MyCompany",[Growths %],BLANK())

Growths % competitors = [Growths % for my company]-SUMX(FILTER(all(FactSales),[Category]=EARLIER([Category])&&[Corporation]<>"MyCompany"&&[Date].[Year]=EARLIER([Date].[Year])),[Growths %])

If above is not help, please provide some detail information about this.

Regards,

Xiaoxin Sheng

[MattAllington]

I can see you have gone to quite some effort - thanks.  Can you just post the workbook?  Just use dropbox or onedrive or something.  This will make it a lot easier.

[Andwu]

Hi @MattAllington, @v-shex-msft,

thanks for the comments and the mockup. It provides me some good explaination of the earlier function.

I created an additional mockup for my scenario. As mentioned, my idea is to apply the category from DimBrand as Filter on Category on FactSales, as the categories of my competitors are not the same as mine.

The formula is highlighted, as it is not working as expected.

Link to the Mockup> Mockup Growths Calculation

Thanks

[v-shex-msft · Accepted solution]

Hi @Andwu,

Sorry for slow response, you can try below measure if it suitable for your requirement:

Growth % Competitors = CALCULATE( [Growth %], ALL(DimBrand[Brand Subcategory]),VALUES(DimBrand[Category]))

Regards,

Xiaoxin Sheng

[Andwu]

Thanks, this was it.

[MattAllington]

Try this

Growths % competitors = CALCULATE( Growths %, ALL (Brand), Values( DimBrand[Category]))

[Andwu]

Hi Matt,

I tried the formula, but as soon as I add Values(DimBrand[Category]), it seems as ALL(Brand) is ignored. I have adjusted my original post, because the reason is maybe that the data model is even more complex.

Best

[MattAllington]

---

@Andwu wrote:

I tried the formula, but as soon as I add Values(DimBrand[Category]), it seems as ALL(Brand) is ignored. 

---

Yes, that is what I would expect.  it is always difficult to help without seeing the data model and the visual you are trying to build.  The formula depends on both.  The visual "adds" filtering to your data model. The formula you write needs to "remove" the filtering you don't need.  

Your growth % formula operates in your own company. You need to remove the company filter but keep the category filter. That's what I thought my suggestion would do.  At e you can work it out from this info. Otherwise please post a sample workbook I can take a look at.