Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.