Multiplication based on a rate table

[sidnir]

Hi all,

I'm slightly new to this so I'll try to explain the problem as best as possible.

My main table contains call data records for devices. Each row is a single data session.

device ID, total data, date (mm-yyyy)

Another table has a reference to device ID and country. Theres a relationship linking device IDs.

device ID, country, etc.

I created a 3rd table which is a static table that is a simple rate card.

country, rate1, rate2

I want to calculate 

Total distinct no. of devices x rate1 for every month.

I tried calculate(distinctcount(device ID) * sum(rate1)) which is the only way I can add rate1 in the forumla, but that's incorrect. It sums up the rate for every row containing the same device id from table1.

I simply want, for each month, number of devices * rate1.

Rate1 is a per device per month rate.

ANy help on this will be appreciated!

Thank you.

[aduguid · Accepted solution]

Try this measure 

Total Device Rate1 per Month = 
VAR DistinctDevices = DISTINCTCOUNT('Call Data Table'[device ID])
VAR DeviceCountry = SELECTEDVALUE('Device Reference Table'[country])
VAR Rate1 = LOOKUPVALUE('Rate Card Table'[rate1], 'Rate Card Table'[country], DeviceCountry)

RETURN 
DistinctDevices * Rate1

[sidnir]

Awesome! Thank you for the quick response!

This worked perfectly!

Thank you so much!! 😊

[Ritaf1983]

Hi @sidnir 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly