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

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

@Anonymous 

https://drive.google.com/file/d/1STDGBeSwvVf6ZUHqHOSFK4TYXmSmDlim/view?usp=sharing

Please share Kudoes and Please mark this as solution




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

19 REPLIES 19
VijayP
Super User
Super User

@Anonymous 

 

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

Please try this! Please share your Kudoes!

Vijay Perepa




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

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.

@Anonymous 

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! 👍

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

hello @VijayP 

added data and desired result in the heading.

Could you please lend a hand?

@Anonymous 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@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.

@Anonymous 

https://drive.google.com/file/d/17KnLGewTPZYq3pCqj3zFi_FGUfwmJeDe/view?usp=sharing

I think it should work




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@VijayP  

no, sir, it doesn`t (

 

if works well when u choose all months

first.PNG

 

but it doesn`t , when u use date filter:

 

second.PNG

@Anonymous 

Its working for me.

VijayP_0-1600955600381.png

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

final.PNGthats what iam talking about @VijayP 

@Anonymous 

Will this Do?

VijayP_0-1600957450246.png

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@VijayP  looks similar.

could u please share the pbix ? 

@Anonymous 

https://drive.google.com/file/d/1STDGBeSwvVf6ZUHqHOSFK4TYXmSmDlim/view?usp=sharing

Please share Kudoes and Please mark this as solution




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@VijayP  thank you so much!

its work!

@Anonymous 

Please share your Kudoes, Many if possible hahahahah 😂




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


@Anonymous 

I thinks this is the approach. 

VijayP_0-1600956339681.png

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@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

amitchandak
Super User
Super User

@Anonymous , 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])))

Anonymous
Not applicable

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.

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.