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.
Hi,
Im trying to get sales amount running value. it is working in descending order but not working in ascending order. can you please provide some suggestion?
Source Data
Expected Output
I used calculation is below
Running Value =
VAR PdtRank =
RANKX(
ALLSELECTED('Sales'[Product Category]),
[SalesAmount] ,,
ASC,
Dense
)
VAR RunningTotal =
CALCULATE(
[SalesAmount],
FILTER(
ALLSELECTED('Sales'[Product Category]),
PdtRank >= RANKX(
ALLSELECTED('Sales'[Product Category]),
[SalesAmount],,
ASC,
Dense
)
)
)
RETURN RunningTotal
I got blank value for all rows. how do I fix it?
thanks,
Ram
Solved! Go to Solution.
Hi @Ramachandran ,
I've fixed it for you now. Please add the highlighted bit to your code and it will work
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
Hi,
Please check the below picture and the attached pbix file.
Running sales amount: =
VAR currentsalesamount = [Sales Amount Measure:]
RETURN
CALCULATE (
[Sales Amount Measure:],
FILTER ( ALL ( Data ), [Sales Amount Measure:] <= currentsalesamount )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Based on your input, I made changes to the measure and then, I'm getting the result is below,
Not showing all the row data
Running sales amount: =
VAR currentsalesamount = [SalesAmount]
RETURN
CALCULATE (
[SalesAmount],
FILTER ( ALL ( Sales ), [SalesAmount] <= currentsalesamount )
)
Hi @Ramachandran ,
On your table, create calculated column called "Sales Rank" to rank sales in ascending order
Next, create another calculated column to compute running total
This should give you the desired output.
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
Based on your inputs, I created Sales Rank and Running Sales measures.
Now, Running value is not showing correctly,
Hi @Ramachandran ,
Please ensure these need to be calculated columns and not measures.
I created Ranking is calculated column and Running Sales is a measure.
I have created both columns in calculated columns but is not working.
for your reference, I have attached .pbix file link.
https://drive.google.com/file/d/1QdUPg6K2-7Jzk-sfbQ012GxROXaVVgol/view?usp=sharing
please check
Hi @Ramachandran ,
I've fixed it for you now. Please add the highlighted bit to your code and it will work
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
Hi @Ramachandran ,
Could you please share the DAX code that you've used to create these columns?
I used the code is below,
Ranking = RANKX(
ALL('Sales'[ProductCategory]),
[SalesAmount],,
ASC,
DENSe
)
Running sales =
var _rank = 'Sales'[Ranking]
var _sum = CALCULATE( Sum('Sales'[SalesAmount]),
FILTER('Sales',
'Sales'[Ranking] <= _rank)
)
return
_sum
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 |
---|---|
114 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |