Max Count Multiplication

[KeatonO]

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. 

[v-janeyg-msft]

Hi, @KeatonO 

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

[KeatonO]

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. 

[MFelix]

Hi  @KeatonO ,

I was abble to drill down your pivot table and get the data for the format you need however I have some questions regarding the calculations based on the PHONE CT AOV, you don't have a match between the values from tier 7 to tier 10?, I see you have direct input to the column K but believe there is an incorrection the values.

Can you check the total opportunity formula since currently you have

Tier 7 - K10

Tier8 - K9

Tier9 - K10

Tier 10 - K10

[KeatonO]

The first dataset, the "Mock data" is the actual data I need help with. the second one was just an example of what u had done in the past in excel, please disregard.  I NEED HELP. Please look at the mock data and tell me how to sum the counts (maxed at 4000) and multiply against the campaign aov. 

[v-janeyg-msft]

Hi, @KeatonO 

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

[KeatonO]

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

[MFelix]

Hi @KeatonO ,

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

[v-janeyg-msft]

Hi, @KeatonO 

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]

Hi @KeatonO ,

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.

[KeatonO]

Thank you very much for helping me with this, I could not figure it out.

I also tried multiplying the formula to sum aov instead of the column and vice versa witht the column row count. 

Dataset and high-level example

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

The old file I'm trying to replicate in Bi won't carry over the formulas to google drive but here are some screenshots. 

End Goal

Backend data and formulas to get results.

Link with reference errors (it's because the formulas are referencing a pivot table and that didn't carry over)

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

Please let me know if there is anything else I can provide or answer to help work through this.