Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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:

 

Example CR.PNG

 

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
magsod
Solution Supplier
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

View solution in original post

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.

v-shex-msft
Community Support
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.
magsod
Solution Supplier
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.