cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anmolgan
Post Prodigy
Post Prodigy

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?
 
 
10 REPLIES 10
v-juanli-msft
Community Support
Community Support

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

This is still not solved.
rajulshah
Solution Sage
Solution Sage

Hello @Anmolgan,

 

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

@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_52vkwS0BqipVLS...

 

https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EXZbi11dbTVArqUsICyGHL4BXh1oPb...

@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))

@rajulshah  This is not working in my case.

Maybe I didn't understand the problem statement. 

Just select Sales group IMF Customer Group IMF OEM in the last page filter, and see the output in the table below (For April Month), Sales Volume KL will be: 18.51 (Coming from my ZSD...query) Royalty will be 24.00 (That is coming from my Spreadsheet), Now if you plot Material.Material Level 01 (column), in the visual level filter and just use advanced filter to say pick only those values where KOBELCO exists, then sales volume KL will change according to that.

 

Now lets say above sales volume KL is y, and the Sales Volume which is basically the multiplication of Sales Volume KL (18.51)coming earlier and the 24.00 royalty is x (which in over case is 1332.79, I want to divide x/y * 100 so that I can get 14.08 as the royalty, and I want to implement this into my existing sales volume formula.

 

Let me know if you still dont understand.

Hi @Anmolgan 

sales volume KL is yroyalty is x,

you want to get new royalty= x/y * 100

then sum [new royalty] *sum [sales volume KL], right?

 

If it is so, i am confused that the final result=x/y * 100*y=x.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I still didn't understand. 
Maybe you can try following:

Sales Volume 2 = 
VAR Test = SELECTEDVALUE('Month Wise Royalty'[Customer Group Name])
VAR Royalty = CALCULATE(SUM('Month Wise Royalty'[Royalty]),FILTER(ZSD_MAT_MARGIN_Q003,SEARCH("KOBELCO",ZSD_MAT_MARGIN_Q003[Material.Material Level 01],1,0)<>0))
RETURN
CALCULATE(SUM(ZSD_MAT_MARGIN_Q003[Sales Volume KL]) * Royalty, FILTER('Month Wise Royalty', 'Month Wise Royalty'[Customer Group Name]= Test))

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors