cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ballist1x
Helper III
Helper III

Removing a Slicer from a measure used ina table that is sliced..?

Hi, i have a table that i created that shows the volume of something and some percentages

 

ultimately i am using a date filter to select the time period over a 13 month period to filter all of the data in the table.

 

i then apply a Month filter (separate column within the data) to get the table to show data for only that month (this year and last year).

 

I am trying to calculate the 13 month total of volume, however, as i have selected the Month filter whenever i use this calculation it is incorrect on the basis that it only returns the value on the Month that i have selected.

 

 

i want the measure to Sum the total volume over that 13 month period i selected via the date slicer, but ignore the Month slicer 

 

and i have treid various versions of this:

 

CALCULATE([Total No DR], ALLEXCEPT('Deal Registrations', 'DR'[Date Logged], 'DR'[Vendor Name]), ALL('DR'[Month Logged]))
 
Total No DR = CALCULATE(COUNT('DR'[Index]))
 

 

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @ballist1x ,

 

One sample for your reference, please check the following steps as below.

 

1. To create a date table and create inactive relationship between the fact table as below.

 

date = CALENDARAUTO()

Capture.PNG

2. To create a measure as below to work the excepted result out.

sumif = 
IF (
    ISFILTERED ( 'date'[date] ),
    CALCULATE (
        SUM ( 'Table'[sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] IN VALUES ( 'date'[Date] ) )
    ),
    CALCULATE (
        SUM ( 'Table'[sale] ),
        USERELATIONSHIP ( 'date'[Date], 'Table'[date] )
    )
)

2.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

1 REPLY 1
v-frfei-msft
Community Support
Community Support

Hi @ballist1x ,

 

One sample for your reference, please check the following steps as below.

 

1. To create a date table and create inactive relationship between the fact table as below.

 

date = CALENDARAUTO()

Capture.PNG

2. To create a measure as below to work the excepted result out.

sumif = 
IF (
    ISFILTERED ( 'date'[date] ),
    CALCULATE (
        SUM ( 'Table'[sale] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] IN VALUES ( 'date'[Date] ) )
    ),
    CALCULATE (
        SUM ( 'Table'[sale] ),
        USERELATIONSHIP ( 'date'[Date], 'Table'[date] )
    )
)

2.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.