cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Table averages not calculating properly when using a measure

Hi everyone,

Hopefully an expert can help me solve this issue I'm having currently.

I have a chart which is visualized based on category and date using the following measure:

SKU Mix Percent =
DIVIDE (
CALCULATE ( SUM ( 'SKU Mix'[Qty] )),
CALCULATE ( SUM ( 'SKU Mix'[Qty] ), ALLEXCEPT ( 'SKU Mix','SKU Mix'[Date] ) )
)

Which results in the following stacked bar chart and table visual:

(Chart visual uses Date, Category, and SKU Mix Percent Measure)

(Table visual uses Category, SKU Mix Percent)

The issue is that the table doesn't match the chart.

(29.8+46.1+37.7+39.8+37.2+93.1)/6 = 47.28%

The table is showing 45.7%

Here's the source table:

How do I write another measure to capture the average show on the chart?

It needs to be a measure becasue it needs to change dynamically once I add some filters.

2 REPLIES 2
Super User

@powerjey , That is a simple avg for that you need a measure like

Averagex(Values( 'SKU Mix[Category] ) , DIVIDE (
CALCULATE ( SUM ( 'SKU Mix'[Qty] )),
CALCULATE ( SUM ( 'SKU Mix'[Qty] ), ALLEXCEPT ( 'SKU Mix','SKU Mix'[Date] ) )
))

You can use line in line Stacked column bar visual

Helper I

Thanks for the response!

So if I were to add one more top-level category that the SKU Mix (Category) is broken down into. How would I incoporate that? Currently if I add a "Product" filter, the percentages don't change dynamically to equal a total of 100% anymore.

Example (added a "Product" column which groups the Category" column into larger overall groups: