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

Frequent Visitor

## Calculate average of one specific retailer

Hi all,

I am stumped with what I think ought to be a fairly simple fix, but I just can't get it to work! I'm creating a field team dashboard where the user selects his or her store from the home page, then that filters all other pages to shows results for only that store. All is good so far, this works fine.

My issue now is that I'd like to show sales performance over time versus the average of that store's retailer. There are four retailers, each with around 400 stores.

The data I have to work with looks like this, two tables linked by the Store Name in a many to 1 relationship

"Call File" table

Store name

Retailer

StoreRef (retailer & store ID)

"Sales" table

Sales

Store name

Brand

Product

The measures I need are these two below, neither of which are working correctly.

Retailer Store Count = CALCULATE(DISTINCTCOUNT(CallFile[StoreRef]),ALL(CallFile[Retailer])) - this is only returning 1s against each store when I look at it in a table, but it should be the count of stores for that store's retailer.

Retailer Sales = CALCULATE(SUM(Sales[Sales],all(CallFile[Store Name])) - again, I can't get this to work and this time it is returning the total sales for all retailers, not the specific retailer for the selected store.

Hopefully this makes sense, and the wisdom of the community elders will prevail once again!

Thanks, Rick

1 ACCEPTED SOLUTION
Microsoft Employee

I assume your Store slicer is from the CallFile table and the relationship is on the StoreName/Store columns.  If so, please try these two modifications to your expressions

Retailer Store Count = CALCULATE(DISTINCTCOUNT(Sales[Store]),ALL(CallFile[StoreName]), VALUES(CallFile[Retailer]))

Retailer Sales = CALCULATE(SUM(Sales[Sales],ALL(CallFile[StoreName]), VALUES(CallFile[Retailer]))

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

8 REPLIES 8
Super User

OP has replied but not @ tagged you.

regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Community Support

Is your problem solved? Can you provide some sample data removing sensitive data if your problem hasn't been solved?

Best Regards,

Caiyun Zheng

Frequent Visitor

No luck yet unfortunately. My "Sales" table looks like this:

It is filtered from slicers based on another "CallFile" table, which determines the results shown on each table. So if it was filtered to show results for store A01, I would need to be able to calculate the average of all stores that are in retailer A. Does that make sense?

Community Support

Actually, the Measures from mahoneypat can get the result you want. If you want to calculate the average of all stores that are in retailer A when selecting store A01, you can do a little change to the second Measure.

Retailer Sales =

VAR tol =

CALCULATE (

SUM ( Sales[Sales] ),

ALL ( CallFile[Store Name] ),

VALUES ( CallFile[Retailer] )

)

VAR num =

CALCULATE (

COUNT ( CallFile[Store Name] ),

ALL ( CallFile[Store Name] ),

VALUES ( CallFile[Retailer] )

)

RETURN

DIVIDE ( tol, num )

The Table visual will look like this:

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Microsoft Employee

I assume your Store slicer is from the CallFile table and the relationship is on the StoreName/Store columns.  If so, please try these two modifications to your expressions

Retailer Store Count = CALCULATE(DISTINCTCOUNT(Sales[Store]),ALL(CallFile[StoreName]), VALUES(CallFile[Retailer]))

Retailer Sales = CALCULATE(SUM(Sales[Sales],ALL(CallFile[StoreName]), VALUES(CallFile[Retailer]))

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

Perfect, thanks you so much @mahoneypat , that worked an absolute treat! VALUES is a new one for me... we live and learn a little more each day!

Microsoft Employee

For your Store Count measure, you will want to put your Store Name and/or StoreRef columns inside the ALL (not retailer).  Not sure your sales measures.  What is the relationship between the tables (which columns and is it single direction)?

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

Hi Pat, thanks for replying - the two tables have a single direction many (Sales) to one (CallFile) relationship using the store name as the key between the two.

So are you saying the Retailer Store Count measure should be: CALCULATE(DISTINCTCOUNT(CallFile[StoreRef]),ALL(CallFile[Storename])) ? I tried this and it just returns the number of all stores in the Call File table and not the number of stores for the retailer of the store that the report is filtered to, sorry.

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 - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors