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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
LouieCorrea
New Member

SUMIFS LESS THAN OR GREATER TO A REFERENCE COLUMN IN DAX

Kindly asking for help.

 

I want to add (or count) certain columns with reference to a column containing whole numbers (1, 2, 3, 4...). The outcome will be based on a filter (single select from [1, 2, 3, 4...]).

 

The logic is:

  • if I select 1 - will sum our count all tagged as 1
  • if I select 2-  will sum our count all tagged as 1 & 2 (basically <= 2)
  • if I select 3 - will sum our count all tagged as 1, 2, and 3 (basically <= 3)

Normally in Excel the formula will be: '=SUMIFS(<Value_to_Add_Column>,<Reference_Column>,"<=" & <Reference_Number>)

 

For the below screenshot: =SUMIFS($B:$B,$C:$C,"<="&$E2)

 

Thank you!

 

LouieCorrea_1-1682585557275.png

 

LouieCorrea_2-1682585585072.png

 

 

 

 

 

1 REPLY 1
johnt75
Super User
Super User

Try

Total Amount =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    'Table'[Group number] <= SELECTEDVALUE ( 'Group table'[Group number] )
)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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