Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Max Count Multiplication

I'm hoping this is an easy solution and I'm just missing it. Here's the deal. I have a set of accounts in a table, each row has data on those accounts with historical spend and has been assigned a tier based on their usage and prior spend. I would like to estimate future value with the historical. 

 

These tiers have been given campaigns and are columns in the table. I have summed and averaged out the totals of the campaigns but it's not possible to contact 100% of that file. so, say we have 57,111 people total and we can only contact 4000 people. I want to cap at 4000 and multiply against that campaign's average order size. 

 

the campaign values are wrong, so tiers 1-6 are core clients, and tier 8 is overstocked. as an example, tier 8 should be 4000*423=1,692,000. That is what the campaign value should be, once I have this, I can drill down further by multiplying across contact rates, conversion rates, and dates. I cannot figure out how to get this piece right though. 

 

I thought by creating a measure for a max count of 4000 and a measure for summing average order value, I could do a sumx and multiply the two but keep getting the same totaled campaign sum and not the max count measure multiplied. 

 

any help is much appreciated. 

10 Replies

  • Hi Anonymous ,

     

    Don't know if the MAX value and the AVERAGES are measures what you need to do is a SUMX but based on a temp table using your columns in this case can be something similar to this:

     

    Campaing VAlue = SUMX(values(Table[Campaigns]), [DialsCount] * [Average AOV18])

     

    If this does not solve your issue can you please share a mockup data or sample of your PBIX file and expected result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support

    Hi, Anonymous 

     

    According to your question, your problem is easy to solve. But the data in the xlsx you provided is too messy, I can't extract the appropriate data for sample. If you can share a sample sample data, I will follow up later. 

     

    Best Regards

    Janey Guo

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello there, I really appreciate the help. I need to get this figured out. with regards to the sample data, you are referring to the  "Power BI Mock Data" correct? that is an export of the actual data I am working with. I just deleted unnecessary data and changed some confidential data. What can I do with the data to make it less "messy" for you? happy to help.