topic Re: Dimenshing Return in DAX Commands and Tips

Dimenshing Return

Aymansg — Thu, 20 Oct 2022 23:21:12 GMT

I am a new user to DAX and wanted to know if it is doable to create a dimenshing return model in power bi. I have been creating it using Excel sheets but wanted to include it in the dashboard.

I have researched and found models for Linear Regression and Correlation but not for Dimenshing Return.

Can someone please advice?

Re: Dimenshing Return

Greg_Deckler — Thu, 20 Oct 2022 23:23:51 GMT

Aymansg Sounds interesting. Should be possible via a DAX while loop I'm thinking. But, would need sample data and expected output to be specific.

Re: Dimenshing Return

Aymansg — Fri, 21 Oct 2022 00:36:49 GMT

Thanks alot for your contribution, I read through you article about for and while loops and will definitely give it a try but can you please advise on what do you mean by expected output?

The dataset is broken by day and it is a real time data that is fetched from a data source.

The dataset includes amount spent and cost per result so there is a maximum amount of spend that we reach before the cost keeps on increasing after.

Leaving the article link here for reference in case anyone wants to check it: https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314

Re: Dimenshing Return

Greg_Deckler — Fri, 21 Oct 2022 12:25:14 GMT

Aymansg Well, really what I need to get started on this is for you to paste in some sample data. Or point me to some sample data.

Re: Dimenshing Return

Greg_Deckler — Fri, 21 Oct 2022 12:39:39 GMT

Aymansg Here is what I did, I ripped off a couple examples from here: Law of Diminishing Returns - Definition, Examples, Diagrams (wallstreetmojo.com)

I recreated these in Power BI. PBIX file is attached below signature. I implemented the diminishing returns as a calculated column. Turned out that this was MTBF and not a while loop. But, could implmenent a while loop to automatically identify where the diminishing returns kicks in.

Re: Dimenshing Return

Aymansg — Fri, 21 Oct 2022 20:13:27 GMT

Here is a dummy sample data for your reference:

Platform Date Spend Cost

Facebook 1 2,366 3.2

Facebook 2 1,577 1.7

Facebook 3 3,627 2.7

Facebook 4 2,636 2.3

Facebook 5 3,326 3.2

Facebook 6 1,782 2.3

Facebook 7 6,213 2,4

Facebook 8 3,163 3.2

Facebook 9 4,118 3.4

I have historical data for two years and can breakdown by day/week/month and for different platforms.