Percentage for the Total is not correct

[Majidbhatti]

I have 2 tables and the output of which is not correct. 

Total of the "Sale Seat"col is adding correctly

Total of Inventory is not adding up because of which the OCCUPANCY is wrong.

Formula of OCCUPANCY is as below

OCCUPANCY = DIVIDE( Sum( 'Table1'[Sale seat] ), Sum('Table2'[Inventory]) , 0 )

[v-lili6-msft]

hi @Majidbhatti 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, [Inventory] is in dim table(table2), so you could refer to these two simple way:

1. In the table1, use RELATED Function to create new Inventory column.

New Inventory = RELATED(Table2[Inventory])

Then adjust the formula as below:

New OCCUPANCY 1 = DIVIDE(SUM(Table1[Sales Seat]), SUM(Table1[New Inventory]))

2.
Just adjust the formula as below:

New OCCUPANCY 2 = DIVIDE(SUM(Table1[Sales Seat]), SUMX(Table1,RELATED(Table2[Inventory])))

Result:

Regards,

Lin

[Majidbhatti]

Hi @v-lili6-msft ,

I guess you forgot to factor that the TABLE1 has repeated DATES.  I have tired both of your suggestion but it seem the Percentage is still not correct.

Any suggestion?

[v-lili6-msft · Accepted solution]

hi @Majidbhatti 

If so, just adjust the formula as below:

New OCCUPANCY 3 = DIVIDE(SUM(Table1[Sales Seat]), SUMX(SUMMARIZE(Table1,Table1[Date],"_Inventory",SUMX(RELATEDTABLE(Table2),[Inventory])),[_Inventory]))

New OCCUPANCY 4 = DIVIDE(SUM(Table1[Sales Seat]), SUMX(SUMMARIZE(Table1,Table1[Date],Table1[Property Code],"_Inventory",SUMX(RELATEDTABLE(Table2),[Inventory])),[_Inventory]))

If not your case, just share a simple sample pbix file and your expected output.

Regards,

Lin

[Majidbhatti]

Thanks a lot.. this did the magic.