Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
3 years ago
Solved

Correct Total Budget Margin Calculation in Table visual

Hi,

 

I have a source data connected to SAP HANA, Direct Query and have some financial data where I calculate the Budget Margin% 

the measures I have created seems to work fine except for the Total Margin% calculation.

calculated column seems not possible due to Direct Query connection.

 

But there are instances in some projects where there is no Sales Price but a Budget cost allocated 

and i would like to exclude these costs from the Total Budget Margin as it impacts the margin% results. Is there a way to fix this issue?

 

These are the measures I am using 

  • Step 1: Total Sales
  • Step 2: Total Budget Cost

 

  • Step 3: Budget Margin

Budget Margin = Sales Price – Budget Cost

 

  • Step 4: Budget%

Budget Margin% = Budget Margin / Sales Price

 

 

 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , jalaomar 

    I get it that you want to exlcude from the total budget margin is all projects where sales price is 0.

    Here are the steps you can refer to :
    (1)This is my test data :

    (2)We can create two measures :

    Budget Margin = 
var _t =FILTER( SUMMARIZE( ALLSELECTED('Project') , 'Project'[Product ID] ,"Sales",[Sales Price],"Budget" , [Budget Cost] ) , [Sales] <> BLANK())
return
IF(HASONEVALUE('Project'[Product ID]), [Sales Price] - [Budget Cost] ,SUMX(_t,[Sales]-[Budget Cost]))
    Budget Margin% = DIVIDE([Budget Margin],[Sales Price])

    (3)Then we can meet your need , the result is as follows:

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    jalaomar First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

    This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

    Also: https://youtu.be/uXRriTN0cfY
    And: https://youtu.be/n4TYhF2ARe8

    • jalaomar's avatar
      jalaomar
      Helper IV
      3 years ago

      Hi,

       

      Just entered my vote!

       

      but just a question, should I create a m_total measure for all measures i have created?

       

      Total sales price

      Total Budget cost

      Budget Margin

       

      BR

      J

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi, jalaomar 

    According to your description, you don't want to calculate the value <0 in the total of [Budget Margin] . Right?

    You can try to use these measures to replace the [Budget Margin] and [Budget Margin%]:

    Budget Margin =

 var _t =FILTER( SUMMARIZE( ALL('Project'[Project ID]) , [Project ID],"Budger Margin" , [Sales]-[Budget total]) , [Budger Margin]>0)

 return

 IF( HASONEVALUE('Project'[Project ID]) , [Sales] - [Budget total] , SUMX(_t , [Budger Margin])

)
    Budget % = IF([Budget Margin] <0 ,BLANK() , DIVIDE([Budget Margin],[Sales]))

    (3)Then we can meet your need , the result is as follows:

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • jalaomar's avatar
      jalaomar
      Helper IV
      3 years ago

      Hello,

       

      Not really actually 

      there are cases where Budget Cost is more than the sales price 

       

      so the correct would, to exlcude from the total budget margin is all projects where sales price is 0.

       

      any idea how to manage that?

       

      Thanks!

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        Hi , jalaomar 

        I get it that you want to exlcude from the total budget margin is all projects where sales price is 0.

        Here are the steps you can refer to :
        (1)This is my test data :

        (2)We can create two measures :

        Budget Margin = 
var _t =FILTER( SUMMARIZE( ALLSELECTED('Project') , 'Project'[Product ID] ,"Sales",[Sales Price],"Budget" , [Budget Cost] ) , [Sales] <> BLANK())
return
IF(HASONEVALUE('Project'[Product ID]), [Sales Price] - [Budget Cost] ,SUMX(_t,[Sales]-[Budget Cost]))
        Budget Margin% = DIVIDE([Budget Margin],[Sales Price])

        (3)Then we can meet your need , the result is as follows:

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly