Skip to main content
cancel
Showing results for
Search instead 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

Frequent Visitor

## Average vs. sum in a measure for a matrix roll up/roll down

Hi,

I am trying to figure out how to best display the order rates  (number of orders divided by the number of clicks) per customer per product.  To illustrate the problem, consider just one customer and 3 products, and let's suppose their order rates for those products in a given time period are 50%, 20%, and 10% because they ordered 2 products out of 4 clicks, 1 out of 5, and 1 out of 10, respectively.

I calculate this measure:

ClickRate = 100*CALCULATE(SUM(Query['Purchases']))/CALCULATE(SUM(Query['Clicks']))

and it shows this:

Time Period

CustomerA

product1             50

product2             20

product3             10

which is fine.

But then when I roll it up to CustomerA as a whole, the measure calculates 21%, which is 100*(2+1+1)/(4+5+10).  What I want instead is the average of those per-product purchase rates, so (50+20+10)/3 = 27%.   But I don't know if it's possible to do different measures depending on roll up/roll down levels in a matrix?  Or is there another solution?

Thank you!

1 REPLY 1
Community Support

Hi @hedgy123 ,

Based on your description, I'm not clear to your data model. Could you please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.(Or screenshot of your data table)

Best Regards,

Yuna

## Helpful resources

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.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors