Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Can anybody help me to figure out how to build a measure that ignores one slicer while respecting the other? I have a Sales table and three slicers - Year, Month and Sales Manager. Year, Month fields are from the related Date table, Sales Manager field is from the Sales table. I need to sum up sales amount for some ProductID respecting date slicers and ignoring Sales Manager slicer. Something like this, but I can't get it work :
Total_of_table2_visual = SUMMARIZE ( FILTER ( ALLEXCEPT ( Date, Date[Year], Date[Month] ), Sales[ProductID] IN { "123" } ), "zz", SUM ( Sales[Amount] ) )
It does not allow me to use Sales table if it is not mentioned in ALLEXCEPT even though Date is related to Sales.
I know this standalone example doesn't make a lot of practical sense, but it is part of a more complex calculation where I want to figure out cost distribution that is not affected by selection of a manager.
Solved! Go to Solution.
OK, this one does the trick. It also respects Dates table rather just working on the native Date column.
Total sales = VAR xMytable = ADDCOLUMNS ( SUMMARIZE ( FILTER ( ALL ( Sales ), Sales[ProductID] IN { "111" } ), Sales[Date], Sales[Manager], Sales[ProductID] ), "zz", SUM ( Sales[Amount] ) ) RETURN CALCULATE ( SUM ( Sales[Amount] ), xMytable )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix , yes, this works. But my measure I am trying to tweak should place the total amount in each row in the visual.
HI @gvg,
Without any data or expected result is difficult to give you a good answer.
Can you share some sample data and expected resutl?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsOK, this is my original Sales table:
And this is what I expect as a result:
I need to get Total Sales for a specific product on each line in the visual (to be able to use that number in further calculation), that respects Date slicer and ignores Manager slicer. My following measure does the trick except that I can not get it work when some Manager is selected in slicer. When I select John, it should show only lines for John with all the numbers for John but with Total sales unchanged (i.e. =140).
Total sales = SUMMARIZE( FILTER ( ALLEXCEPT(Sales,Sales[Date]), Sales[ProductID] IN { "111" } ), "zz", SUM ( Sales[Amount]) )
Ideally I'd like to use Dates table as a slicer for dates.
Here's my test file.
OK, this one does the trick. It also respects Dates table rather just working on the native Date column.
Total sales = VAR xMytable = ADDCOLUMNS ( SUMMARIZE ( FILTER ( ALL ( Sales ), Sales[ProductID] IN { "111" } ), Sales[Date], Sales[Manager], Sales[ProductID] ), "zz", SUM ( Sales[Amount] ) ) RETURN CALCULATE ( SUM ( Sales[Amount] ), xMytable )
Glad you could solve it.
Don't forget to mark the response as correct.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |