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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

By passing page filters in measure

Hi there,

 

i want to sum up sales for an item on a report page that is filtered but the sales sum for the item should not be affected by the filter. I want to specify in the measure for which dates and article group the sales sum should be shown. 

 

I have the following Example Table:

Unbenannt.PNG

 

Now the report page is filter for Year 2020 but the measure for this one item (1) should give me the sales sum for 2019 and also only for the Garden category. 

 

Right now I'm stuck with this: 

 

Spoiler
revenue 2019 =

VAR _table = ALL('Example Table')
RETURN
CALCULATE(SUMX(_table;[Sales]);
FILTER(_table;[Year]=2019);
FILTER(_table;[Article Group]="Garden"))

With this measure PBI sums up all sales though and does not apply any filtering. Why is that and how can i fix this?

 

Thank you!

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous 

All will ignore all, the filter. That is why is advised to star-Schema structure and use all on the needed table. Like You can year and Article as a separate dimension

-- Avoid all will not allow filtering 2019 if 2020 is selected. And All on 'Example Table' will remove other filters too

Measure =
CALCULATE(SUMX('Example Table';[Sales]);
FILTER(;[Year]=2019);
FILTER('Example Table';[Article Group]="Garden"))


Measure =
CALCULATE(SUMX(_table;[Sales]);
FILTER(All('Year');[Year]=2019)) // Avoid ; FILTER('Example Table';[Article Group]="Garden"), if it is slicer

 

Or like this

Measure =
CALCULATE(SUMX(_table;[Sales]);
FILTER(All('Year');[Year]=2019);
FILTER('Example Table';[Article Group]="Garden"))

 

Refer this document

https://docs.microsoft.com/en-us/power-bi/guidance/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

v-gizhi-msft
Community Support
Community Support

Hi,

 

Because the formula SUMX(ALL(Table),[Sales]) you used will calculate the total sum of original table regardless of any filter.

Please try this measure:

revenue 2019 = 
VAR _table =
    ALL ( 'Table' )
RETURN
    CALCULATE (
        SUM ( [Sales] ),
        FILTER ( _table, [Year] = 2019 && [Article Group] = "Garden" )
    )

The result shows:

60.PNG

 

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

7 REPLIES 7
v-gizhi-msft
Community Support
Community Support

Hi,

 

Because the formula SUMX(ALL(Table),[Sales]) you used will calculate the total sum of original table regardless of any filter.

Please try this measure:

revenue 2019 = 
VAR _table =
    ALL ( 'Table' )
RETURN
    CALCULATE (
        SUM ( [Sales] ),
        FILTER ( _table, [Year] = 2019 && [Article Group] = "Garden" )
    )

The result shows:

60.PNG

 

Hope this helps.

 

Best Regards,

Giotto

amitchandak
Super User
Super User

@Anonymous 

All will ignore all, the filter. That is why is advised to star-Schema structure and use all on the needed table. Like You can year and Article as a separate dimension

-- Avoid all will not allow filtering 2019 if 2020 is selected. And All on 'Example Table' will remove other filters too

Measure =
CALCULATE(SUMX('Example Table';[Sales]);
FILTER(;[Year]=2019);
FILTER('Example Table';[Article Group]="Garden"))


Measure =
CALCULATE(SUMX(_table;[Sales]);
FILTER(All('Year');[Year]=2019)) // Avoid ; FILTER('Example Table';[Article Group]="Garden"), if it is slicer

 

Or like this

Measure =
CALCULATE(SUMX(_table;[Sales]);
FILTER(All('Year');[Year]=2019);
FILTER('Example Table';[Article Group]="Garden"))

 

Refer this document

https://docs.microsoft.com/en-us/power-bi/guidance/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
glissando
Resolver II
Resolver II

The ALL function ignores any / all filters and returns the entire table. Also not sure why you are using a variable to set the table.

This measure should work.

Thanks

 

revenue 2019 =

CALCULATE(
SUMX('Sales','Sales'[Sales]),
FILTER('Sales','Sales'Year]=2019)
FILTER(_'Sales','Sales'[Article Group]="Garden")
)

Anonymous
Not applicable

The problem with this is that when i select a different filter for the year - for instance 2018 - the measure shows a blank value because all the 2019 values get filtered out. So therefore i was thinking about creating a table that is untouched from any filter selection in the dasboard and filter that for the specific combination of year = 2019 and category = garden. Because i always want to show this number no matter what the other selections of the filters in the dashboard are. 

 

I know i could also deactive the interactions between this table and the filters of the dashboard but for that i would need to deactive also all other interactions from others visuals. And that seems to me to be way to much work. 

Hi,

 

If you want to sum the filtered sales and always includes [Year]=2019&[Article Group] = "Garden" row sales value, please try this:

revenue 2019 = 
VAR _table =
    ALL ( 'Table' )
RETURN
    CALCULATE (
        SUM ( [Sales] ),
        FILTER ( _table, [Year] = 2019 && [Article Group] = "Garden" )
    )
        + SUM ( 'Table'[Sales] )

Hope this helps.

 

Best Regards,

Giotto

Sorry, I missed the part on the "page filter set to 2020"...

 

Create a measure for total sales:  Total Sales = SUM('Sales'[Sales])

Create a measure for 2019 total sales: CALCULATE ([Total Sales], 'Sales'[Year] = 2019)

 

You can make this dynamic by using variables for the year...

    2019_Sales =
        VAR Curr_Year = MAX(Sales[Year])
        VAR Prior_Year = Curr_Year - 1
    RETURN
        CALCULATE([Total_Sales], Sales[Year] = Prior_Year)

 

Hopefully, this is what you are looking for.

Thanks

With the article group included...

 

    2019_Sales =
        VAR Curr_Year = MAX(Sales[Year])
        VAR Prior_Year = Curr_Year - 1
        VAR Article_Group = "Garden"
    RETURN
        CALCULATE([Total_Sales], Sales[Year] = Prior_Year, Sales[Article Group] = Article_Group)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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