cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## DAX - calculate average of averages

Here's an example similar to my real-world problem: https://www.dropbox.com/s/lyrca5gx07xe0zy/Example%20CR.pbix?dl=0

As you can see conversion rate is calculated as SUM(Bought) / SUM(Seen). However, I have been struggling to create an average of conversion rate and display it for the selected group. Here's how the example looks like:

The 85.06% is the SUM of all Bought / SUM of all Seen. What I want is an average of conversion rate for each row. The Bought and Seen in my real case are measures calculated from another table, so it is not possible to create a column that will hold the Conversion rate, but it needs to be a measure (for slicing and dicing purposes from the other table).

The correct number displayed should be: 80.07%.

1 ACCEPTED SOLUTION
Solution Supplier

You could use the function AVERAGEX to iterate all products and for each individual product you calculate the conversion rate, then the function will calculate the average of all your averages.

The code needs to be adjusted to work in your particular real case scenario, but in the example you provided you could use the following:

ConversionRateAverage =
AVERAGEX (
VALUES ( 'Sheet1'[Products] ),
CALCULATE ( DIVIDE ( SUM ( 'Sheet1'[Bought] )SUM ( 'Sheet1'[Seen] ) ) )
)

Br,

Magnus

3 REPLIES 3
Anonymous
Not applicable

Thanks for your help @magsod and @v-shex-msft

I found @magsod solution more clear,  but the one with summarize table might also work in some scenarios.

Community Support

Hi @Anonymous,

You can try to create a summary variable table, then use AVERAGEX function to get average:

```AVG =
AVERAGEX (
SUMMARIZE (
ALLSELECTED ( Table ),
[Product],
"A", SUM ( [A] ),
"B", SUM ( [B] ),
"Div", SUM ( [A] ) / SUM ( [B] )
),
[Div]
)
```

Notice: if your data contains any privacy data, please do mask sensitive data before sharing.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Solution Supplier

You could use the function AVERAGEX to iterate all products and for each individual product you calculate the conversion rate, then the function will calculate the average of all your averages.

The code needs to be adjusted to work in your particular real case scenario, but in the example you provided you could use the following:

ConversionRateAverage =
AVERAGEX (
VALUES ( 'Sheet1'[Products] ),
CALCULATE ( DIVIDE ( SUM ( 'Sheet1'[Bought] )SUM ( 'Sheet1'[Seen] ) ) )
)

Br,

Magnus

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.