Showing results for 
Search instead for 
Did you mean: 
Post Prodigy
Post Prodigy

Calculate overall Average in a table without filtering



I have two tables with 4 fields,


Table 1 (Cases) :    Qty (Gap) and Member


Table 2 (Revenue): Sales (Regularization) and Qty (Gap)


  • For each member I want to calculate the total Regularization (Sales)  / Total Quantity (Gap) in table 1 giving me an Aerage Revenue per qty 
  • Then I want to multiply table 2   Qty  times the Average sales per qty. 
Average Revenue = CALCULATE(DIVIDE(sum(Revenue[Regularization]),Sum(Revenue[Revenue Gap])),ALLSELECTED(Revenue))

Gap Forecast = [Average Revenue] * sum('Cases'[Case Gap])
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.
Does anyone know why this is happening and have a solution?

Hi @lcasey,


Please try this:

Average Revenue = DIVIDE(sum(Revenue[Regularization]),Sum(Revenue[Revenue Gap])) 


For more advice, please provide sample data and illustrate the desired result with examples. How to Get Your Question Answered Quickly



Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.