Skip to main content
Showing results for 
Search instead 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
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 =
    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:

Screenshot 2023-03-10 110749.jpg


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. 

Super User
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

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: 

Screenshot 2023-03-10 130333.jpg

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.