cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LarBo Frequent Visitor
Frequent Visitor

Calculating printing cost of different proposal scenarios that have different rinter models

Hi all, 

 

I need to compare different proposals of leasing printing services for the next five years based on the quantity I've printed the last years.

I have information of clicks made in several printers, now as we are changing provider I want to know how much this would have cost if I would have choose proposal 1 or proposal 2, these are description of my tables:

1.- Clicks: Number of clicks by idPrinter made monthly

2.- Printer: idPrinter with idModel and place of the printers

3.- PrinterModels&Costs: idModel with cost of each click

4.- Proposals: idPrinter with exercise of using different model grouped by proposals (original, Approach 1, Approach 2), and a column of renew ->1: Renew contract, 0: NOT renew contract.

Could you help me please to show the different scenarios for these same printers usign different proposals?

Attached is an excel with data, thank you!

 

File with data

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

Re: Calculating printing cost of different proposal scenarios that have different rinter models

Hi @LarBo ,

 

If the context changes, the formula should be changed too. Please try this one.

TotalCosts2 =
SUMX (
    'Proposals',
    CALCULATE ( SUM ( Clicks[Clicks BN] ) ) * RELATED ( Costs[B&W Click Cost] ) * Proposals[Renew]
)

Calculating-printing-cost-of-different-proposal-scenarios-that-have-different-rinter-models1

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Calculating printing cost of different proposal scenarios that have different rinter models

Hey,

 

your request reminds me to the way some of my customer projects start. Wondering what my fellow community members will come up with.

 

Regards,
Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Microsoft v-jiascu-msft
Microsoft

Re: Calculating printing cost of different proposal scenarios that have different rinter models

Hi @LarBo ,

 

If I understand your requirements correctly, the key point here is the relationship. Please download the demo from the attachment. 

TotalCosts =
SUM ( Clicks[Clicks BN] ) * MIN ( Costs[B&W Click Cost] )
    * MIN ( Proposals[Renew] )
LowestCosts =
CALCULATE (
    MINX (
        SUMMARIZE (
            'Proposals',
            Proposals[idPrinter],
            Proposals[idModel],
            Proposals[Approach],
            "totalCosts", [TotalCosts]
        ),
        [totalCosts]
    ),
    ALL ( Proposals[idModel], Proposals[Approach] )
)

Calculating-printing-cost-of-different-proposal-scenarios-that-have-different-rinter-models

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LarBo Frequent Visitor
Frequent Visitor

Re: Calculating printing cost of different proposal scenarios that have different rinter models

Hi Jiascu, when I try to open the file you shrared give me an error that is not possible to open the file, I've donwloaded several times, could you send another link/file please?

 

thank you

Microsoft v-jiascu-msft
Microsoft

Re: Calculating printing cost of different proposal scenarios that have different rinter models

Hi @LarBo ,

 

Please try this Onedrive Link. Maybe you need the latest version of Desktop to open the file.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LarBo Frequent Visitor
Frequent Visitor

Re: Calculating printing cost of different proposal scenarios that have different rinter models

Hi Jiascu, I got the file thank you.

 

I think we are almost there, because filtering some id_printer in Clicks table, for example:  T1129060913 I'll have this result in image 1, which sum of clicks would be 1741 clicks.

print1.PNGimage 1

So using Original Approach it gives me 51,28, that is correct, because in that we're assuming MODEL 1 that cost 0,0294540229885057 each click, multiplicating gives 51,28. Same happens if we choose Approach 1 for example where the model for that printer is MODEL11 which has 0,0198 as a click cost, giving result 34,47. GREAT!

 

Is missing that sum of all approach gives correct results, as you can see in image 2, total appears 0 in approaches 0, 1 and 2, but Original and should sum all, for example for approach 1 it should be 1650,19. I think is not necessary lowest cost...

idPrinteridModelApproachTotalCosts
T1129060912MODEL 11Approach 10
T1129060913MODEL 11Approach 134,47
T1129060919MODEL 13Approach 1121,97
T1129462795MODEL 13Approach 1188,09
T423M330026MODEL 11Approach 1324,11
T423M330029MODEL 12Approach 10
W523M550060MODEL 13Approach 113,96
W523M550077MODEL 12Approach 161,37
W523M550090MODEL 12Approach 135,22
W524M650007MODEL 12Approach 165,31
W863C300186MODEL 12Approach 1102,7
W863C300238MODEL 13Approach 10
W863C300245MODEL 12Approach 187,6
W912P905750MODEL 13Approach 1473,18
W914P906829MODEL 13Approach 140,08
W914P906832MODEL 13Approach 175,83
W915P500146MODEL 13Approach 126,3

 

It would be excellent if I could compare totals of approach in a bar graph or something like it.

 

Also, I got doubt in formula for total cost because it's usign MIN:

TotalCosts = sum(Clicks[Clicks BN]) * MIN(Costs[B&W Click Cost])*MIN(Proposals[Renew])
In fact, I think it should calculate the real cost not MIN, something like this:
TotalCosts = sum(Clicks[Clicks BN]) * Related(Costs[B&W Click Cost])*Related(Proposals[Renew])

but this gives me an error that doesn't exist a relationship between tables... why happens?

 

Thank you!

 

Microsoft v-jiascu-msft
Microsoft

Re: Calculating printing cost of different proposal scenarios that have different rinter models

Hi @LarBo ,

 

If the context changes, the formula should be changed too. Please try this one.

TotalCosts2 =
SUMX (
    'Proposals',
    CALCULATE ( SUM ( Clicks[Clicks BN] ) ) * RELATED ( Costs[B&W Click Cost] ) * Proposals[Renew]
)

Calculating-printing-cost-of-different-proposal-scenarios-that-have-different-rinter-models1

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)