Forum Discussion

ALambert's avatar
ALambert
Frequent Visitor
4 years ago
Solved

Creating custom columns that count/sum rows from one table based on values from another

I have a list of transactions, each with a dollar amount. I'm interested in creating several metrics about various ranges of the dollar amounts for the transaction (such as 0-30 or 150-300). 

 

I managaed to make BI measures that counted the number of rows with transaction amounts in a given range fairly easily with something like:

 

 

Range_3k = COUNTAX(FILTER(Gift, AND(Gift[amount]>=3000, Gift[amount]<6000)), Gift[amount])

 

 

where "Gift" is the gift table containing all gifts, gift[amount] is the transaction dollar amount, and the ranges are literal integers. The problem with this is that for any metric (count, sum, average, etc), I then have to create a measure for each range, so not the most efficient way when you have 16 ranges.

 

My idea was to create a new table with the ranges and their metrics. So in this case, I have the columns (range name, minimum amount, maximum amount, count, sum).

I'm getting stuck with how to count or sum the gifts for a given range though. I've tried creating a new column and using DAX expressions and using Power Query, but somehow I feel like I'm going about this the wrong way.

 

How would other people here go about making an untold number of metrics for a given range of data within a table? I can't imagine manually making 50+ measures is the only way to do this.

2 Replies

    • ALambert's avatar
      ALambert
      Frequent Visitor

      Thanks for the link jennratten! I haven't quite gotten it working yet (this is more of side project I'm doing), but this definitely feels like the right way forward.