Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Ignore a data field when graphing average of daily sales

I have a table which has sales information categorized by date and age group (See screenshot). I'm trying to create a measure/ column that can give me the total sale for the day (regardless of the age group) but i want to keep the age group field in the table.

 

image.png

 

The goal is to graph the average of this new measure(daily sale) against date. Current graphs are affetced by the age group field, understandably it brings the average value down.  

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @Anonymous 

According to your description and sample pictures, I can clearly understand the output you want to get, you can try this measure:

Expected Measure =

CALCULATE(

    SUM('Table'[Sales Amount]),

    FILTER(ALL('Table'),

    [Date]=MAX('Table'[Date])&&[Retailer ID]=MAX('Table'[Retailer ID])))

Then create a table chart and place it like this:

v-robertq-msft_0-1615882623554.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

Hi, @Anonymous 

According to your description and sample pictures, I can clearly understand the output you want to get, you can try this measure:

Expected Measure =

CALCULATE(

    SUM('Table'[Sales Amount]),

    FILTER(ALL('Table'),

    [Date]=MAX('Table'[Date])&&[Retailer ID]=MAX('Table'[Retailer ID])))

Then create a table chart and place it like this:

v-robertq-msft_0-1615882623554.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisOlson
Super User
Super User

The standard way to do this is by removing that filter context.

 

CALCULATE ( SUM ( Table1[Sales Amount] ), REMOVEFILTERS ( Table1[Age Group] ) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors