Forum Discussion
Calculate items percent based on slicers selection
- 2 years ago
Hi quinnie, your approach is correct.
- Calculation of "Items" is straightforward: when you write sum(quantity) it should calculate a correct sum of currently selected items (rememebr that "currently selected" is defined not only by filters but by other values in the table as well, if you want to know more about it read this article - Filter context in DAX - SQLBI
- total_items is the trickiest: first what "total" means for you? Same country but all sales channel within the selected time period? or may be everything (so all countries, all sales channel all time periods)? Once you defined what "Total" means, you'll need to use Calculate() function to overwrite one (or all) filters that are applied at the moment of calcualtion (Introducing CALCULATE in DAX - SQLBI)
- items_percent - once you have 2 measures from above, this one can be defined exactly as you described: items / total_items
If any further support is needed, please provide more details, share screenshot of your semantic model and details on what exactly is working differently from what you expect.
Good luck! - Anonymous2 years ago
Hi,quinnie Hello,Sergii24 ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below
You need to use the all and allselected functions
Here are the results of my tests:items = CALCULATE(SUM('Table'[quantity]),ALLSELECTED('Table'))Total_items = CALCULATE(SUM('Table'[quantity]),ALL('Table'))itempercent = DIVIDE('Table'[items],'Table'[Total_items])The difference between ALL() and ALLSELECTED() is that ALL() is not affected by external filters, while ALLSELECTED() is affected by external filters
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi quinnie, your approach is correct.
- Calculation of "Items" is straightforward: when you write sum(quantity) it should calculate a correct sum of currently selected items (rememebr that "currently selected" is defined not only by filters but by other values in the table as well, if you want to know more about it read this article - Filter context in DAX - SQLBI
- total_items is the trickiest: first what "total" means for you? Same country but all sales channel within the selected time period? or may be everything (so all countries, all sales channel all time periods)? Once you defined what "Total" means, you'll need to use Calculate() function to overwrite one (or all) filters that are applied at the moment of calcualtion (Introducing CALCULATE in DAX - SQLBI)
- items_percent - once you have 2 measures from above, this one can be defined exactly as you described: items / total_items
If any further support is needed, please provide more details, share screenshot of your semantic model and details on what exactly is working differently from what you expect.
Good luck!
- andy8081 year ago
Resolver I
I am having a similar challenge: I have multiple slicers all from fields in Table A and a Date slicer from Table B. I am looking at TableA[CU] for my calculations. I want to calculate the % of CUs based on my selection (multiple selections perhaps) OF the last selection CU amount. Not from the WHOLE. Example: Total CU = 100. July has 50CU and Aug has 50CU. When I select July - the measure calculates 50%. THEN I select from a slicer (say X Workspace) and that value is 25CU. The answer I need is 50%, not 25%. I have a relationship between Table A table B by Date