topic Count of Locations with Sum of Values between min-max values in DAX Commands and Tips

Count of Locations with Sum of Values between min-max values

JoeCochran — Wed, 14 May 2025 16:38:35 GMT

Hope I can describe this problem correctly! I have 2 tables, not joined to each other:

FACT Table

Location	Category A	Category B	Value
1	A	Z	51
1	B	X	100
2	A	Z	56
2	C	Y	105
3	B	W	61
3	D	Q	110
4	A	Q	52
4	A	Z	102

Min/Max Table:

Min	Max
150	160
160	170
170	180
...continuing pattern

...with each table having many, many more rows. What I am trying to do is come up with a DAX formula that will allow me to build a table visual with the following output:

Min	Max	Count of Locations with Sum of [Values] between min/max
150	160	2
160	170	1
170	180	1

The DAX I have tried for the "Count of Locations..." column is this:

Count of Locations =
var minV = MIN(GroupsTbl[Min])
var maxV = MAX(GroupsTbl[Max])
var sumValues = SUM(FactTbl[Value])

var locCount = CALCULATE(DISTINCTCOUNT(FactTbl[Location]),FILTER(FactTbl,sumValues>=minV),FILTER(FactTbl,sumValues<maxV))

return locCount

...but this returns an output that is not paritioned by the Location column. Something like this using the example above:

Min	Max	Count of Locations with Sum of Values between min/max
640	650	4

This would be pretty easy to solve by pre-aggregating the table to remove the 2 "Category" columns - but I need to be able to filter with slicers on those columns, and have the count of locations (and sum of values) update based on the selected values from the slicers.

I hope this is enough explanation - apologies if not! Happy to provide more info/answer questions! 🙂

Any help would be greatly appreciated. Thanks -Joe

Re: Count of Locations with Sum of Values between min-max values

lperr — Wed, 14 May 2025 17:23:44 GMT

Hey Joe,

This formula should provide the desired output based on the details required:

Count of Locations =
VAR MinValue = MIN(GroupsTbl[Min])
VAR MaxValue = MAX(GroupsTbl[Max])
RETURN
    COUNTROWS(
        FILTER(
            SUMMARIZE(
                FactTbl,
                FactTbl[Location],
                "LocationSum", SUM(FactTbl[Value])
            ),
            [LocationSum] >= MinValue && [LocationSum] < MaxValue
        )
    )

What This Formula Does

SUMMARIZE:
- This groups the FactTbl by Location and calculates the total Value for each location. The result is a temporary table with two columns: Location and LocationSum.
FILTER:
- It filters the summarized table to include only the rows where LocationSum falls between the Min and Max values from GroupsTbl.
COUNTROWS:
- Finally, it counts how many rows (locations) meet the filter criteria.

Why Use This Approach?

Pre-Aggregation: By summarizing the data upfront, the formula simplifies the calculation and reduces complexity.
Dynamic Filtering: It works seamlessly with slicers applied to Category A and Category B since it directly references FactTbl.
Performance: This method is faster and more efficient than alternatives like EARLIER or nested SUMX, especially when working with large datasets.

Re: Count of Locations with Sum of Values between min-max values

techies — Wed, 14 May 2025 18:33:34 GMT

Hi JoeCochran please try this

Location Count in Range =

VAR MinVal = SELECTEDVALUE(BandTable[Min])

VAR MaxVal = SELECTEDVALUE(BandTable[Max])

RETURN

CALCULATE(

COUNTROWS(

FILTER(

ADDCOLUMNS(

VALUES(Fact[Location]),

"TotalValue", CALCULATE(SUM(Fact[Value]))

[TotalValue] >= MinVal && [TotalValue] < MaxVal

)

Re: Count of Locations with Sum of Values between min-max values

JoeCochran — Wed, 14 May 2025 19:54:32 GMT

Thanks to both for the help!!