cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cosminc Post Partisan
Post Partisan

ignore a filter in a measure

Hi,

i have a data base like  one with columns: with date (by day), a column with hour (hour by hour) and other column with sales values

and i want to obtain in a table like this:

 

date                        hour         sales              sales0 (only for 15 may 2018)????how

20 may 2018              10              100             100

20 may 2018             11               70                  70

20 may 2018             12               10                 30

20 may 2018            13                50                   5

 

i made a measure like :

Sales 15may = CALCULATE(average(table[sales]),table[date]="15 may 2018")

 

if i select on date filter other date than 15 may 2018 the results are blank

can anyone to help me please with the right formula? i'm stucked in this

 

Thanks!

Cosmin

1 ACCEPTED SOLUTION

Accepted Solutions
avramioana Helper I
Helper I

Re: ignore a filter in a measure

Hi,

 

I hope i understood correctly your question.

 

Below is the formula i have used to calculate sales for one specific date (in my case it is Revenue for 1January2000):

           Sales 1Jan2000 = CALCULATE(

                                                  Sum(Sales[Revenue]),

                                                  FORMAT('Date'[Date], "MM/DD/YYYY") = "01/01/2000"

                                                  )

The table is showing now Sales for Date 1January2000 regardless of the Date filter and whenever the filter is changed the Sales for 1st Jan remain the same. A variance can be calculated after that.

 Capture.JPG

Regards,

Ioana

              

View solution in original post

7 REPLIES 7
quentin_vigne Solution Sage
Solution Sage

Re: ignore a filter in a measure

Hi @cosminc

 

I can give you something but I think that's not what you want.

 

Why do you need sales for the 15 may 2018 ? To make a difference between two dates ? 

 

 

You can add graphs / card box to your file and choose wich one will filter other visuals or not 

cosminc Post Partisan
Post Partisan

Re: ignore a filter in a measure

Hi 

yes i want to make another measure with % variation, not diffence but that's the point

the example with date was for an easier understanding, in reality my base has other column - i just want to add a column which contains data for a specific value form a column; and when i choose other value in filter i want that specific column to stay with fixed data and to compare with the column with the data for the value selected

can you give me a formula?

thnaks a lot!

Cosmin

avramioana Helper I
Helper I

Re: ignore a filter in a measure

Hi,

 

I hope i understood correctly your question.

 

Below is the formula i have used to calculate sales for one specific date (in my case it is Revenue for 1January2000):

           Sales 1Jan2000 = CALCULATE(

                                                  Sum(Sales[Revenue]),

                                                  FORMAT('Date'[Date], "MM/DD/YYYY") = "01/01/2000"

                                                  )

The table is showing now Sales for Date 1January2000 regardless of the Date filter and whenever the filter is changed the Sales for 1st Jan remain the same. A variance can be calculated after that.

 Capture.JPG

Regards,

Ioana

              

View solution in original post

cosminc Post Partisan
Post Partisan

Re: ignore a filter in a measure

ok your measure

the variance not work, is it because i have to make these two measures as columns?

thanks

Highlighted
alexei7 Continued Contributor
Continued Contributor

Re: ignore a filter in a measure

Hi cosminc,

 

Am I right in saying you are trying to compare the hours of a certain day (May 15th) with another?

 

I don't think you need a calculated column, the following two measures worked for me.

 

1) Measure = CALCULATE(sum(YourTableName[sales]),filter(ALLEXCEPT(YourTableName,YourTableName[hour]),YourTableName[date]=DATE(2018,05,15)))

2) Variance = [Measure]-sum(Sheet1[sales]) 

cosminc Post Partisan
Post Partisan

Re: ignore a filter in a measure

thanks

i used the solution from Ioana

thanks a lot to all for help!

cosminc Post Partisan
Post Partisan

Re: ignore a filter in a measure

Hi again

i have another issue regarding this topic

2 data set conected between them and a slicer with data from the first one; on the second when i put your measure, in the syntax doesn't work if i put dimension ignored to be the dimension which i use in that slicer

how can i do o correct sync and measure?

Thanks!

Cosmin

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors