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 team
Currently when I use this formula:
Solved! Go to Solution.
Hi, @GraceTCL
According to your description and sample pictures, I can clearly understand your meaning, and I think the AllExcept() function can perfectly help you to achieve the expected output, you can transform the measure like this:
This is the test data I created based on your picture:
Sum of revenue =
CALCULATE(SUM(Sales[Revenue]),ALLEXCEPT(Sales,Sales[Date],Sales[Vertical]))
Then I created three slicers and a card chart to make a test:
And you can get what you want.
More info about the AllExcept() function in DAX
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @GraceTCL
According to your description and sample pictures, I can clearly understand your meaning, and I think the AllExcept() function can perfectly help you to achieve the expected output, you can transform the measure like this:
This is the test data I created based on your picture:
Sum of revenue =
CALCULATE(SUM(Sales[Revenue]),ALLEXCEPT(Sales,Sales[Date],Sales[Vertical]))
Then I created three slicers and a card chart to make a test:
And you can get what you want.
More info about the AllExcept() function in DAX
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@GraceTCL , Not very clear, Try like
Vertical_Revenue = CALCULATE([Revenue],Filter(allselected('Sales'),'Food data'[Vertical] = max('Food data'[Vertical]) && 'Food data'[Date] = max('Food data'[Date])))
@amitchandak Thanks for your reply.
My apologies. Let me rephrase as above suggestion doesn't work. Hope you can advise!
My current dax formula should be like that as per eg:
Vertical_Revenue = CALCULATE([Revenue],ALLEXCEPT('Sales','Sales'[Vertical],'Sales'[Date]'))
And the expected results and wrong results are:
Essentially, I dont want the Vertical summation to be affected by the explicit filters on all the subsegments. However, it has to be the sum of revenue in the selected verticals based on the selected date range.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
106 | |
75 | |
44 | |
39 | |
33 |
User | Count |
---|---|
165 | |
90 | |
65 | |
46 | |
43 |