Forum Discussion

quinnie's avatar
quinnie
New Member
2 years ago
Solved

Calculate items percent based on slicers selection

Hi everyone, I'm new to Power BI and need some help with the following issue: I have a table called RTYTD with these columns:   I want to calculate the items_percent based on the following ...
  • Sergii24's avatar
    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!

  • Anonymous's avatar
    Anonymous
    2 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.