cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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.
1 ACCEPTED SOLUTION
Microsoft

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 )
)
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

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 )
)
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Cherie,

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors