cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KeatonO
Helper II
Helper II

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. 

Powerbi help.png

 

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 10
v-janeyg-msft
Community Support
Community Support

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

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. 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

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.

vjaneygmsft_0-1624585853420.png

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

 

Best Regards

Janey Guo

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. 

KeatonO_4-1624627621980.png

 

 

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

 

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

 

MFelix_0-1624962321862.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

vjaneygmsft_1-1624959340075.png

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
Super User III
Super User III

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

KeatonO_3-1624376141402.png

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

KeatonO_0-1624375883769.png

 

Backend data and formulas to get results.

KeatonO_1-1624375921328.png

KeatonO_2-1624375971532.png

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.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors