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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
robderay
Frequent Visitor

Integrating a certain measure to matrix table to show data on the unselected month slicer

Hi team - 

 

Kindly request for your assistance on scenario I'm trying to create visual on my report. The thing was I'm using Net Profit to show on matrix visual with 2 slicers (one for Financial Year, and one for text based month). Then the thing was I want to add another measure which is to show the budget for those unselected months. For e.g. on my table I had click month from Jul-Nov FY20. And then I wanted to add or to use if conditions to show the budget based from budget summary table to those unselected months. I already create disconnected table but can't get to add the budget measure on the matrix table? 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

HI @robderay,

You can try to use except function to extract the unselected value and stored to a variable, then you can add 'all' filter on your table with conditions to check records in the variable list.

measure =
VAR unselected =
    EXCEPT ( ALL ( Table[Column] ), ALLSELECTED ( Table[Column] ) )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALL ( Table ), [Column] IN unselected )
    )

Dax EXCEPT 

The IN operator in DAX 

If above not help, can you please share some sample data for test?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

HI @robderay,

You can try to use except function to extract the unselected value and stored to a variable, then you can add 'all' filter on your table with conditions to check records in the variable list.

measure =
VAR unselected =
    EXCEPT ( ALL ( Table[Column] ), ALLSELECTED ( Table[Column] ) )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALL ( Table ), [Column] IN unselected )
    )

Dax EXCEPT 

The IN operator in DAX 

If above not help, can you please share some sample data for test?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

amitchandak
Super User
Super User

I hope you had a date calendar.

 

Rolling 4 = CALCULATE(sum(Budget[Budget Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),4,MONTH))

Rolling 4 = CALCULATE(sum(Budget[Budget Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date])+1,-4,MONTH))

 

Now if you can find a variable to get no month selected and use in place of 4

month diff = datediff(min(Date[Date]),max(Date[Date]))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

 

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

I'm not sure if I'm getting this? Do the rolling measure would show those months that was not selected on the filter and get the budget for those months and still can be visualized on the repor? 

Typically date table can move around, if not we can use all(date) in the filter. If it does not work just share sample data and sample output.

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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