Forum Discussion
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
- MFelixSuper User
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.- AnonymousNot applicable
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.
- v-janeyg-msftCommunity 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
- AnonymousNot 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.
- v-janeyg-msftCommunity Support
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