Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
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:
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!
Solved! Go to Solution.
@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/
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:
Hope this helps.
Best Regards,
Giotto
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:
Hope this helps.
Best Regards,
Giotto
@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
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")
)
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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
63 | |
52 | |
47 |
User | Count |
---|---|
218 | |
86 | |
64 | |
63 | |
60 |