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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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])))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.