create %share measure with repeated column value

[p_kaushal]

Sample of my data]-The Total Base actually comes from a small table with year weekno and total base column which I create a relationship on year week no

yearweekno	region	Format	Sales TY	Sales LY	Total Base
201652	Asia	Online	764	680	63916
201652	US	Store	856	795	63916
201652	Asia	Store	603	842	63916
201652	UK	Online	894	662	63916
201701	Asia	Online	925	845	89125
201701	US	Store	837	776	89125
201701	Australia	Online	845	675	89125
201701	UK	Store	793	799	89125
201702	Australia	Store	659	678	98456
201702	UK	Online	981	592	98456
201702	Asia	Store	514	520	98456
201702	US	Online	648	964	98456

The measure I want to calculate is %Share(of base)=(Sales TY-Sales LY)/Total Base

If I use a measure in PowerBI for this it would look like
%Share= (SUM('Table'[SalesTY])-sum('Table'[SalesLY]))/min('Table'[Total Base])

Even if I use max it wouldn't be different at week no levels
%Share= (SUM('Table'[SalesTY])-sum('Table'[SalesLY]))/max('Table'[Total Base])

It would look like the below table which would be same for min or max in the denominator since it would take only value to divide as it should

yearweekno	Sales TY	Sales LY	%Share
201652	3117	2979	0.22%
201701	3400	3095	0.34%
201702	2802	2754	0.05%

The problem arises when you use a 'card' or only one week-it takes the total row and the data looks like this

201702	2802	2754	0.0%
Total	2802	2754	0.1%

This (0.1% i.e incorrect) takes the min base of the entire column when it should have taken only 201702

There is no way I can change that in card (as in donot show totals or something)

The problem might arise again if the slicer has only one week no and region level %share is calculated.

How do I arrive at a formula that shall help me get the corresponding denominator to divide in the %share calculation as min max donot work.

I have some pages with weekly line charts and %share as well as pages where it is just a week slicer with region %share
How do I make sure the formula works universally?

[v-ljerr-msft]

Hi @p_kaushal,

According to your description above, I have tested it with your sample data and the formulas. I am not sure I can reproduce your issue. It seems to work all fine for me.

%Share = 
 ( SUM ( 'Table1'[Sales TY] ) - SUM ( 'Table1'[Sales LY] ) )
    / MAX ( 'Table2'[Total Base] )

Here is the sample pbix file for your reference.

Regards

[p_kaushal]

Thanks for having a look.Yes, that's how it should work.Somehow the total row % share doesn't show correct values in my Power BI.

It's a weird error !

[v-ljerr-msft]

Hi @p_kaushal,

Yes, a little weird. Could you share a sample pbix file which can reproduce the issue? So that I can help further investigate on this issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[p_kaushal · Accepted solution]

Sorry for the late reply!

I was able to solve the issue.The issue was not related to the formulas but it was related to the data connections and relationships between my tables which I had made between tables which was duplicating the dimension metric and hence max(base) was changing.

Thanks for looking into it though.Much appreciated.