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 ti...
Need Help
v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, Anonymous 

 

The data you released at the beginning is close, but this is a screenshot of visual. I don't know which is data, which is measure or column. I hope you show the data you need to use in the form of a table, and then describe the logic of the calculation result.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

 

Best Regards

Janey Guo

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I'm reproposing the question and answering some questions in one message. 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. 

 

Here is a link to the data 

https://drive.google.com/file/d/1f0qokkiAkoDJ_sFlyhAX5etKMDzMoLyn/view?usp=sharing

 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    The data you provided is not representative, I can’t test it. So I can only give you advice.

    You need to create the two measures and multiply them according to the conditions. 

    Sorry, I modified the reply. When I opened the link yesterday, I saw only some data, which may be a network problem. And su has replied later, I hope your problem can be solved.

    If you have other question, you can feel free to ask me.

     

    Best Regards

    Janey Guo

     

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    Has v-janeyg-msft  refered the information is not representative but believe I have found a way but cannot confirm the values. Create the measure MAX:

    MAX dial counts = if (COUNT(Sheet2[BPN]) <= 4000, COUNT(Sheet2[BPN]), 4000)

    Now add the following measure:

    TotalCampaing = 
SUMX (
    SUMMARIZE (
        Sheet2,
        Sheet2[TIER_DESCRIPTION],
        "Maxdialcounts", [MAX dial counts],
        "AV18MOS", AVERAGE ( Sheet2[AOV_18MOS] )
    ),
    [Maxdialcounts] * [AV18MOS]
)

     

    Result below and in attach PBIX