Forum Discussion
Create a Measure: Sum totals for created data ranges
Hi,
I have a table (Claimant Transactions) that houses transaction data. One of the fields is (Total Paid). These values range from $0 to $10,000,000.
I am trying to create a visualization table that breaks down these transactions in to ranges. In order to create the ranges I built a table in Power BI using the 'Enter Data' function. I called it (Bin Select). I also added an Index column so I can sort it with ease. See below:
In order to build the visualization, I created a table and made a New Measure. I called the New Measure (Number of Total Customers). See below:
Number of Total Customers =
IF(HASONEVALUE('Bin Select'[Min Value Range]),
COUNTROWS(FILTER('Claimant Transactions',
[Total Paid]>=VALUES('Bin Select'[Min Value Range]) &&
[Total Paid]<VALUES('Bin Select'[Max Value Range])
)
),
COUNTROWS('Claimant Transactions')
)
With this, I now have a Visualization (Table) of the following:
My problem is: I want to create another measure that will sum the (Total Paid) for each range. I want to add this measure to my visualization and I can't seem to write it correctly.
Can you please help me write this formula (Measure)?
Thank you!
Andrew
Hi Anonymous
Try this
Total Paid = SUMX(FILTER('Claimant Transactions', [Total Paid]>=MIN('Bin Select'[Min Value Range]) && [Total Paid]<MAX('Bin Select'[Max Value Range]) ), [Total Paid] )
5 Replies
Hi Anonymous ,
you need to use an aggregator measure should be something similar to this:
Total Paid = SUMX(FILTER('Claimant Transactions', [Total Paid]>=VALUES('Bin Select'[Min Value Range]) && [Total Paid]<VALUES('Bin Select'[Max Value Range]) ) ), [Total Paid] )Seems like one additional )
Total Paid = SUMX(FILTER('Claimant Transactions', [Total Paid]>=VALUES('Bin Select'[Min Value Range]) && [Total Paid]<VALUES('Bin Select'[Max Value Range]) ), [Total Paid] )
Thank you for the continued help but that didn't work either. The syntax is correct and doesn't contain an error. However, it came back with an error within the visualization:
I am not sure what the issue is. Is the problem that there are multiple values of the same transaction amount? How do I go about handleing that? I also verified that the (Total Paid) field is a whole number in the (Claimant Transaction) table:
The New Measure in the Bin Select table is also set to whole number:
Pretty stumped here now.
Hi Anonymous
Try this
Total Paid = SUMX(FILTER('Claimant Transactions', [Total Paid]>=MIN('Bin Select'[Min Value Range]) && [Total Paid]<MAX('Bin Select'[Max Value Range]) ), [Total Paid] )
