Forum Discussion

sidnir's avatar
sidnir
Regular Visitor
2 years ago
Solved

Multiplication based on a rate table

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.

 

 

 

Need Help
  • aduguid's avatar
    aduguid
    2 years ago

    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

3 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    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's avatar
      sidnir
      Regular Visitor
      2 years ago

      Awesome! Thank you for the quick response!

      This worked perfectly!

      Thank you so much!! ğŸ˜Š