I am using this DAX to calculate My Sales Volume:
@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:
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))
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.
sales volume KL is y, royalty is x,
you want to get new = x/y * 100
then sum  *sum [ ], right?
If it is so, i am confused that the final result=*y=x.
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))
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!
Watch Session 22 Ted's Dev Camp along with past sessions!
Click here to read more about the May 2022 updates!