Calculate overall Average in a table without filtering

Question

Hello,&nbsp;I have two tables with 4 fields,&nbsp;Table 1 (Cases) : &nbsp;&nbsp; Qty (Gap) and Member&nbsp;Table 2 (Revenue): Sales (Regularization) and Qty (Gap)&nbsp;For each member I want to calculate the total&nbsp;Regularization (Sales)&nbsp; / Total Quantity (Gap) in table 1 giving me an Aerage&nbsp;Revenue per qty&nbsp;Then I want to multiply table 2 &nbsp; Qty&nbsp; times the Average sales per qty.&nbsp;Average Revenue = CALCULATE(DIVIDE(sum(Revenue[Regularization]),Sum(Revenue[Revenue Gap])),ALLSELECTED(Revenue))Gap Forecast = [Average Revenue] * sum('Cases'[Case Gap])&nbsp;The problem is when I add a visual that includes The member and qty from table 1 which may be filtered and try to calculate it against the Average Revenue , The Average Revenue calculation also gets filtered and does not provide a valkue per member.&nbsp;Does anyone know why this is happening and have a solution?&nbsp;&nbsp;&nbsp;Thanks!

v-yulgu-msft · Answer

Hi&nbsp;lcasey,
&nbsp;
Please try this:
Average Revenue = DIVIDE(sum(Revenue[Regularization]),Sum(Revenue[Revenue Gap])) 
&nbsp;
For more advice, please provide sample data and&nbsp;illustrate the desired result with examples.&nbsp;How to Get Your Question Answered Quickly
&nbsp;
Regards,
Yuliana Gu

Forum Discussion

1 Reply

Related content