Calculating distinct count of items based on average sales thresholds, summarized by fiscal year

[wirednorth]

Hello!

First, thank you to all the up-until-now users of this forum-- the community content has helped me out of a number of jams, and I've learned a bunch. I haven't quite been able to successfully search for what's tripping me up here, though.

I'm having a heck of a time computing a particular summary of item sales performance: the goal is to aggregate all active products by fiscal year, and to separate those items into buckets according to their average sales per week, per location. The output should look something like this, in which each of the threshold columns sum to equal a count of total active products:

However, my output keeps turning out like this:

Here's what I have currently for DAX:

Example of the (improperly functioning) threshold calculations:

Average Sales < 0.10 =

CALCULATE(

DISTINCTCOUNT('Data Table'[Item ID]),

FILTER('Data Table',[Avg. Sales per Store] < 0.1)

)

Other measures used:

Avg. Sales per Store = [Gross Sales] / [Store Opp Count]

Gross Sales = SUM('Data Table'[UNITS_SOLD])

Store Opp Count = SUM('Data Table'[ACTIVE_LOCATION_CT])

(the idea here is that the [Avg. Sales per Store] measure always reflects (Sales/# of Stores), regardless of the number of locations or weeks in consideration.)

And here's an example of how the underlying data is structured:

Just to throw in some additional information: I also tried calculating the threshold measures in an alternate way (based on some prior searches); it yielded the correct evaluations at the item level (in that, when drilled to item, each fell into the correct bucket based on the [Avg. Sales per Store] calculation), but it did not aggregate to Fiscal Year correctly. Example of that DAX:

Avg. Sales < 0.10 =

VAR AvgSalesStore = SUM('Data Table'[UNITS_SOLD])/SUM('Data Table'[ACTIVE_LOCATION_CT])

RETURN

CALCULATE(

DISTINCTCOUNT('Data Table'[Item ID]),

FILTER('Data Table', AvgSalesStore < .1)

)

Thanks for your help, and please let me know if you'd like me to provide any additional detail.

[v-cherch-msft · Accepted solution]

Hi @wirednorth 

You may refer to this post to create the measure.For example:

Count =
VAR _table =
    SUMMARIZE (
        'Data Table',
        'Data Table'[FISC_YR_NM],
        "Avg", SUM ( 'Data Table'[UNITS_SOLD] ) / SUM ( 'Data Table'[ACTIVE_LOCATION_CT] )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Data Table'[Item ID] ),
        FILTER ( _table, [Avg] < 0.1 )
    )

If it is not your case,please share a simplified sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

How to Get Your Question Answered Quickly

Regards,

Cherie

[wirednorth]

Cherie,

Thank you for your guidance on this. I was able to get everything working quite quickly with the solution you outlined.