Forum Discussion

MrMP's avatar
MrMP
Icon for Helper III rankHelper III
3 years ago
Solved

Replicating Excel calculation in power bi

Hi All,

 

I am trying to replicate the excel formula in power bi. 

Here is the structure:

The only known is sales per month (yellow row). Other fields are calculations. Is it possible to create something like this in power bi?

I am attaching excel for easier formula exploration.

 

Hope you can help! Thank you!

Need Help

4 Replies

  • Shaurya's avatar
    Shaurya
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi MrMP,

     

    I have checked your excel file and I think you easily create all those fields in Power BI. I suggest that you go for DAX Columns. You should with creating Beginning Rec by using something like:

     

    Beginning Rec = IF('Table'[Month]=EARLIER('Table'[Month])+1,'Table'[Ending Receivable],null)

     

    Then you can go on and create the other fields which are arithmetic operations like addition or multiplication.

     

    Did I answer your question? Mark this post as a solution if I did!
    Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

     

    • MrMP's avatar
      MrMP
      Icon for Helper III rankHelper III
      3 years ago

      Thank you for prompt answer, but all this has to stay in measures since % field is controlled by numeric parameter.

    • MrMP's avatar
      MrMP
      Icon for Helper III rankHelper III
      3 years ago

      Shaurya Do you have any idea how this can be achived with measures?

      • Shaurya's avatar
        Shaurya
        Icon for Memorable Member rankMemorable Member
        3 years ago

        Hi MrMP,

         

        Since your formulas require that you reference various calculated fields based on a row level context, I really think you should go for columns. You can write conditions for the % field if it is quantised.

         

        Did I answer your question? Mark this post as a solution if I did!
        Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI