Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Calculating Variance with data from same text field

    Posted Aug 18, 2022 05:24 PM
    Hi,

    I have my dataset as below  which means i am not sure how to calculate the variance between Actual and Budget and Actual and Forecast as they are all in same field called scenario. The values are in one single field "Values" New to DAX please help.

    When i display it, it is across Income and Expenses col.  See below eg.

    Thanks





    ------------------------------
    Nishesh ved
    Management Accounting
    ------------------------------


  • 2.  RE: Calculating Variance with data from same text field

    Bronze Contributor
    Posted Aug 19, 2022 04:38 AM

    Hi Nishesh,
    I have a few pointers for you here.

    1. The first thing that I would do is to sort out the table with the Scenario column. I will call this table 'Facts'. The Facts[Scenario] column is good.  Keep it that way. What I would change is the Facts[Period] column and remove all the aggregation rows like YTD and FY. Then add a Facts[Date] column that is most likely the last date of each month (if monthly numbers are the most fine-grained in your dataset). I have written an article about this here where I call the equivalent table 'Journals' ABC of Power BI: 'J' is for Journals | AccountingWEB .

    2. The next thing that I would do is to create a 'Dates' dimension table with a 1:Many relationship from Dates[Date] to Facts[Date]. Here is an article that I wrote about the power of filtering dates in Power BI, with links to further articles and videos on the subject. ABC of Power BI: 'D' is for Date | AccountingWEB

    3. Finally, the simplest approach, I believe, would be to create three Measures [Actual], [Budget] and [Forecast]. You can do this using Calculate. For YTD etc., personally I would avoid using the DAX Time Intelligence functions, as I mention in the article.

    These three steps will get your basic setup. Beyond this, you can add many more bells and whistles to make the report more intuitive and interactive, but getting the data model correct in the first place as I outline here is where I would start. I hope this is helpful.
    Hugh



    ------------------------------
    Hugh Johnson
    Dublin, Ireland
    ------------------------------



  • 3.  RE: Calculating Variance with data from same text field

    Posted Aug 25, 2022 08:54 AM

    Hi Nishesh,

    What you can do is just create dax formula to separate the 3 scenarios then calculate the variance e.g. Actual = Calculate(Sum(value),scenario = "Actual").

    Regards,
    RD



    ------------------------------
    Ardie Trinidad
    Senior Finance Analyst
    ------------------------------