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

[JoeCochran]

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

[JoeCochran]

Thanks to both for the help!!

[techies]

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

        )

    )

)

[lperr · Accepted solution]

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

1. 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.

2. FILTER:

   • It filters the summarized table to include only the rows where LocationSum falls between the Min and Max values from GroupsTbl.

3. 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.