Calculate average of one specific retailer

[briktheprop2]

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

[PhilipTreacy]

@mahoneypat 

OP has replied but not @ tagged you.

regards

Phil

[v-cazheng-msft]

Hi, @briktheprop2 

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

Best Regards,

Caiyun Zheng

[briktheprop2]

@v-cazheng-msft 

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? 

[v-cazheng-msft]

Hi, @briktheprop2 

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.

[mahoneypat · Accepted solution]

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

[briktheprop2]

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! 

[mahoneypat]

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

[briktheprop2]

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.