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

[LarBo]

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

[v-jiascu-msft]

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] )
)

Best Regards,

[LarBo]

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

[v-jiascu-msft]

Hi @LarBo ,

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

Best Regards,

[LarBo]

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.

image 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...

idPrinter	idModel	Approach	TotalCosts
T1129060912	MODEL 11	Approach 1	0
T1129060913	MODEL 11	Approach 1	34,47
T1129060919	MODEL 13	Approach 1	121,97
T1129462795	MODEL 13	Approach 1	188,09
T423M330026	MODEL 11	Approach 1	324,11
T423M330029	MODEL 12	Approach 1	0
W523M550060	MODEL 13	Approach 1	13,96
W523M550077	MODEL 12	Approach 1	61,37
W523M550090	MODEL 12	Approach 1	35,22
W524M650007	MODEL 12	Approach 1	65,31
W863C300186	MODEL 12	Approach 1	102,7
W863C300238	MODEL 13	Approach 1	0
W863C300245	MODEL 12	Approach 1	87,6
W912P905750	MODEL 13	Approach 1	473,18
W914P906829	MODEL 13	Approach 1	40,08
W914P906832	MODEL 13	Approach 1	75,83
W915P500146	MODEL 13	Approach 1	26,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!

[v-jiascu-msft · Accepted solution]

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]
)

Best Regards,

[TomMartens]

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