Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am using this DAX to calculate My Sales Volume:
Hi @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:
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))
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 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.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |