Skip to main content
cancel
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.

Reply
powerjey
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 =
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)

CHART.jpg

 

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. 

2 REPLIES 2
amitchandak
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.

powerjey_0-1678471612874.png

 

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

Screenshot 2023-03-10 130333.jpg

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.