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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
irukandjix
Regular Visitor

Matrix row and column totals with excluded measure values

Table without restirictions has two dimensions Color and Number. Value cells has values from a measure (we can call it CellSum) that sums up values with these dimensions. I want to exclude all values below 4 from counted totals, but want to see these values in the matrix. See example. How can we achive this with dax code?

 

Table without restrictions:

irukandjix_0-1701441057280.png

 

Desired table (red lines just to show which values we want to exclude from totals):

irukandjix_1-1701441103532.png

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @irukandjix 

I would suggest a measure along these lines (replace table/column references as needed):

CellSum Modified = 
VAR Threshold = 4
RETURN
    IF (
        AND ( ISINSCOPE ( Number[Number] ), ISINSCOPE ( Color[Color] ) ),
        [CellSum],
        SUMX (
            SUMMARIZE ( YourTable, Color[Color], Number[Number] ),
            VAR CellSum = [CellSum] RETURN IF ( CellSum >= Threshold, CellSum )
        )
    )

ISINSCOPE is used to detect whether you are in an individual Color/Number cell.

 

Does something like this work for you?

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
irukandjix
Regular Visitor

Very nice solution, thank you @OwenAuger ! Your magic does exactly what is wanted!

OwenAuger
Super User
Super User

Hi @irukandjix 

I would suggest a measure along these lines (replace table/column references as needed):

CellSum Modified = 
VAR Threshold = 4
RETURN
    IF (
        AND ( ISINSCOPE ( Number[Number] ), ISINSCOPE ( Color[Color] ) ),
        [CellSum],
        SUMX (
            SUMMARIZE ( YourTable, Color[Color], Number[Number] ),
            VAR CellSum = [CellSum] RETURN IF ( CellSum >= Threshold, CellSum )
        )
    )

ISINSCOPE is used to detect whether you are in an individual Color/Number cell.

 

Does something like this work for you?

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors