cancel
Showing results for
Did you mean:
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
Community Support
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
Post Prodigy
This is still not solved.
Solution Sage

Hello @Anmolgan,

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

Post Prodigy

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

Solution Sage

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))``````
Post Prodigy

@rajulshah  This is not working in my case.

Solution Sage

Maybe I didn't understand the problem statement.

Post Prodigy

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.

Community Support

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.

Solution Sage

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

Announcements

#### The Power BI Community Show

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

#### Charticulator Design Challenge

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

#### Check it Out!

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