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
admin11
Memorable Member
Memorable Member

Does PBI expression can have sales for 2020 ?

Hi All

Does PBI allow us to create a expression that compute 2020 sales amount , i mean even user filter year=2021 , that expression will still display 2020 sales.

Paul

1 ACCEPTED SOLUTION

@admin11 

Yes there is that's exactly what ALL is for, it removes any filters .

Here's a link to your PBIX file - check the Measure/Card I created.

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

14 REPLIES 14
PhilipTreacy
Super User
Super User

@admin11 

 

Hi Paul,

I just realised that @v-deddai1-msft  already answered your question and I just posted the same thing.  My bad, this new forum layout is confusing.

Looking at the image you posted you are missing the ALL() in the measure. 

all-missing.png

 

Just copy/paste this code rather than typing it out.

 

This is what should work and does work for me in your PBIX file

SALES_IN_2020 = CALCULATE( SUM(SALES[sales]),FILTER(ALL('Date'),'Date'[Year] = 2020))

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Philip

I think now i am convince that PBI is not allow to have expression that will not affected when user set filter . i mean if user set year filter =2019 , there is no way any expression can display 2020 sales amount.

Paul

@admin11 

Yes there is that's exactly what ALL is for, it removes any filters .

Here's a link to your PBIX file - check the Measure/Card I created.

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

@admin11 

 

Hi Paul,

Sorry I couldn't get the download from Dropbox, I kept getting an error.

To do what you want you need to use ALL('Date') to remove the slicer filters for Year

Try this

SALES_IN_2020 = CALCULATE( SUM(SALES[sales]),FILTER(ALL('Date'),'Date'[Year] = 2020))

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil

I just try , when i use your expression , when i select year =2019 , the sales amount dis-appear.

admin11_0-1611814419094.png

https://www.dropbox.com/s/nmyuuszuwdawcmb/PBT_V2021_127%20sales%20not%20affect%20by%20filter%20by%20...

Above link is my PBI file

 

Paul

v-deddai1-msft
Community Support
Community Support

Hi @admin11 ,

 

I can't get your pbix file from your link. Would you please share it again. 

 

There are many ways to filter data for 2020. You'd better have a date table , and you can set a slicer or page level filter.

 

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

 

Best Regards,

Dedmon Dai

Hi @admin11 ,

 

You can fix filter in the formula:

 

SALES_ = CALCULATE( SUM(SALES[sales]),FILTER(all('Date'),'Date'[Year] = 2020))

 

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

 

Best Regards,

Dedmon Dai

 

Hi All

Your expression work fine , when i dont filter year ,  but when i select year = 2019 , it display blank.  what i need is even when user filter year=2021 , the display still can display 2020 sales amount.

admin11_0-1611810125355.png

 

Hi @admin11 ,

 

I have modified the original reply, please try again.

 

Best Regards,

Dedmon Dai

PhilipTreacy
Super User
Super User

 @admin11 

 

Hi Paul,

The short answer would be yes.

But need to see data to do it for you.

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


@admin11 

 

Hi Paul,

 

Sorry I've only just seen this. I didn't know you'd replied.

When you reply you need to @ mention people so they get notified.

When typing your reply just type the @ symbol and a list of names will appear for those who have responded in the thread.  Click on the one(s) you want to notify that you've replied.

I can't access your file, Dropbox says it's been deleted.

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.