cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Allexcept and slicer

Hello, guys!

I think the solution is not  difficult, but I still can't find it. Could you help me?

The task  - find measure average by filtered date perion in pivot table by each objects (object - rows, month - columns).

For this, i use such formula:

test = CALCULATE ( measure, ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ))


and it works fine, till i have to use month slicer to change full year to 10 month, for example.

 

The "test" still calculate the whole year average.
How could I avoid it? And get dynamic average, depends on choosing months?

 

Lind to db: https://gofile.io/d/VLBPTf

 

How it should be (11 months):

Avg sales between dates
25 849,87
25 849,87
25 849,87
25 849,87
25 849,87
25 849,87
25 849,87
25 849,87
25 849,87
25 849,87
25 849,87
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: @Re: Allexcept and slicer

19 REPLIES 19
Highlighted
Super User IV
Super User IV

Re: Allexcept and slicer

@KonstantinD , Try if one of these can work

 

This year = CALCULATE([Measure],DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

 

This Year = CALCULATE([Measure],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper IV
Helper IV

Re: Allexcept and slicer

hi @amitchandak 

there are no working formulas, unfortunately. 
And moreover, it shouldn't be used only for current/last year.
Cause, pivot table is filtered by years also.

Highlighted
Solution Sage
Solution Sage

Re: Allexcept and slicer

@KonstantinD 

 

Calculate(Measure,DATESBETWEEN(Dates[Date],MIN(Dates[Date]),MAX(Dates[Date])))

Please try this! Please share your Kudoes!

Vijay Perepa

Highlighted
Helper IV
Helper IV

Re: Allexcept and slicer

hi @VijayP 

nice to see you

i tried it. This formula gives the each month measure.

However, I need to find average measure on choosing period using slicer.

 

Ok, I will prepare data.

Highlighted
Solution Sage
Solution Sage

Re: Allexcept and slicer

@KonstantinD 

Try that measure what you calculate is showing Average by using AVERAGE or AVERAGEX function and then incorporate in my Formula and hope that works! 👍

 

Highlighted
Helper IV
Helper IV

Re: Allexcept y slicer

@VijayP

i use such formula as measure:

SUMX(SUMMARIZE(table_1, object), CALCULATE(AVERAGEX('Calendar', CALCULATE(SUM(value), filter(table_2, category)))))
so, I get Average by columns and sum by rows.
What should I add to this formulla to get average value in every column ?
As I told before CALCULATE ( measure, ALLEXCEPT ( 'Calendar', 'Calendar'[Year] )) works good, but do wrong calculation when using dynamic month filter
Thats the problem

Highlighted
Helper IV
Helper IV

@Re: Allexcept and slicer

hello @VijayP 

added data and desired result in the heading.

Could you please lend a hand?

Highlighted
Solution Sage
Solution Sage

Re: @Re: Allexcept and slicer

@KonstantinD 

https://drive.google.com/file/d/1dhaLDcmJaJ4v-0iTTKV77sKtgfcm0V-l/view?usp=sharing

Please find attached file with solution. let me know whether you are looking for this

Highlighted
Helper IV
Helper IV

Re: @Re: Allexcept and slicer

@VijayP  https://gofile.io/d/uBbbpZ

no, it doesn`t work (

 

It should works like "Average sales per FY" but be date filter depended.

 

For example, if you choose 1-11 months,

it should be 25 849,87 in every cell.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors