SUM number of rows based on filter interval and values from other table

[eriweste]

Problem

Visualize the amount of compensations that should be payed to customers based on there consumption in specific months

Compensation is based on intervals and are stored in separate table "MasterIntervals"

Ex:

Consumption, customer and dates are stored in "ReadingTable"

Based on the interval the customers consumption is related to, thay are entitled to compensation

We need to visualize and SUM the total amount of compensation for each "interval"

Ex: Based on the Month (filter) "December", 3870 customers (one per row) are in the interval "100"

= 3870 * 100

ANy ideas ?

Br

erik

[v-janeyg-msft · Accepted solution]

Hi, @eriweste 

You need to calculate the number of rows then multiply by the corresponding compensation.

Like this:

NUMBER =
CALCULATE (
    DISTINCTCOUNT ( 'ReadingTable'[InstNum] ),
    'ReadingTable'[Month] = SELECTEDVALUE ( 'ReadingTable'[Month] )
)

Total =
[NUMBER] * SELECTEDVALUE ( 'MasterIntervals'[Compensation] )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey