cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Prodigy

## Calculate overall Average in a table without filtering

Hello,

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?

Thanks!
Employee

Hi @lcasey,

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

Regards,

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors