Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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,
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,
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
Hi @LarBo ,
Please try this Onedrive Link. Maybe you need the latest version of Desktop to open the file.
Best Regards,
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.
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:
but this gives me an error that doesn't exist a relationship between tables... why happens?
Thank you!
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,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |