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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
GraceTCL
Helper II
Helper II

Filter and dax formula

Hi team

 

Currently when I use this formula:

Vertical_Revenue = CALCULATE([Revenue],ALLEXCEPT('Sales','Food data'[Vertical],'Food data'[Date]')) 
I get the result which is a summation of the values against the orange column.
vertical and segment.PNG
However what I want is the summation of the result in the green column where
1) It is the summation of revenue based on the explicit filter of Vertical and Date 
2) Segments are not affected by explicit filter.
 
However when I apply above formula, I realize that verticals which dont contain the segment will be completely excluded from the summation consideration. Pls advise how can I correct my formula to achieve the desired result.
 
Thank you!
1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1619516082727.png

 

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:

v-robertq-msft_1-1619516082732.png

v-robertq-msft_2-1619516082738.png

 

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1619516082727.png

 

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:

v-robertq-msft_1-1619516082732.png

v-robertq-msft_2-1619516082738.png

 

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.

amitchandak
Super User
Super User

@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:

vertical and segment.PNG

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.