Forum Discussion

Anmolgan's avatar
Anmolgan
Post Prodigy
6 years ago

How to calculate Dynamic DAX?

I am using this DAX to calculate My Sales Volume: 

Sales Volume = Var Test= SELECTEDVALUE('Month Wise Royalty'[Customer Group Name])
Return
CALCULATE(Sum(ZSD_MAT_MARGIN_Q003[Sales Volume KL]) * Sum('Month Wise Royalty'[Royalty]), FILTER('Month Wise Royalty', 'Month Wise Royalty'[Customer Group Name]= Test))
 
Now I want to divide [Sales Volume]/ Royalty, now if I write simple dax function to divide the values, it gives me inital values for a partcuilar customer grouop, some how its not dividing the royalty by the product which is (Sum(ZSD_MAT_MARGIN_Q003[Sales Volume KL]) * Sum('Month Wise Royalty'[Royalty]) 
 
Does anyone have any idea what is going wrong?
 
 
Need Help
Show and Tell

10 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    6 years ago

    Hello Anmolgan,

     

    Maybe if you can provide sample structure and expected results, I could help you? 

    • Anmolgan's avatar
      Anmolgan
      Post Prodigy
      6 years ago

      rajulshah  Below is what I require:

       

      I need to calculate royalty for each customer group, for example in my pbix file attached in the last page there is a page level filter that I have applied, now I want to calculate royalty for only those sales volume where KOBELCO words comes, now I do have a dax currently where I multiply my royalty by overall sales volume for IMF OEM (See Sales Volume Measure), now this is bound with a spreadsheet and most of the values are coming correctly aspect some of them in the measure.

       

      I need to calculate overall royalty for IMF OEM whereever KOBELCO name comes, now royalty changes each month hence the formula should be dynamic, attached here the spreadsheet and powerbi files:

       

      https://ifitech-my.sharepoint.com/:x:/g/personal/anmol_ganju_ifi_tech/ESN94ct0bqNCt-h_52vkwS0BqipVLSHKQjfrQL2ayLyxSQ?e=fGpiaA

       

      https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EXZbi11dbTVArqUsICyGHL4BXh1oPbybw2M4RYiAGeoctg?e=Diw7rX

      • rajulshah's avatar
        rajulshah
        Resident Rockstar
        6 years ago

        Anmolgan,

         

        I hope the following DAX helps you.

        Sales Volume = 
VAR Test = SELECTEDVALUE('Month Wise Royalty'[Customer Group Name])
VAR Royalty = CALCULATE(SUM('Month Wise Royalty'[Royalty]),FILTER(ALL(ZSD_MAT_MARGIN_Q003[Material.Material Level 01]),CONTAINS(ZSD_MAT_MARGIN_Q003,ZSD_MAT_MARGIN_Q003[Material.Material Level 01],"KOBELCO")),FILTER('Month Wise Royalty','Month Wise Royalty'[Customer Group Name] = Test))
RETURN
CALCULATE(SUM(ZSD_MAT_MARGIN_Q003[Sales Volume KL]) * Royalty, FILTER('Month Wise Royalty', 'Month Wise Royalty'[Customer Group Name]= Test))
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anmolgan 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case?
    If not, please feel free to let me know.
     
    Best Regards
    Maggie