How to calculate Dynamic DAX?

[Anmolgan]

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?

[v-juanli-msft]

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

[Anmolgan]

This is still not solved.

[rajulshah]

Hello @Anmolgan,

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

[Anmolgan]

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

[rajulshah]

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

[Anmolgan]

@rajulshah  This is not working in my case.

[rajulshah]

Maybe I didn't understand the problem statement. 

[Anmolgan]

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.

[v-juanli-msft]

Hi @Anmolgan 

sales volume KL is y, royalty 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.

[rajulshah]

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