Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Calculation Help with filters on visual

I have the following data, by day. this is an example of Multiple days data. 

 

Momentcall_idcall_time
Widget b45091055171665904/15/2021
Widget a45052815198781404/15/2021
Widget b45052815198781404/15/2021
Accessory a48251648875560904/15/2021
Widget a45104852083015604/16/2021
Widget b45104852083015604/16/2021
Accessory a45102819691069404/16/2021
Widget b45102819691069404/16/2021
Battery45239660704563204/17/2021

 

and I need to show a filtered selection of products on a visual and the % they show up vs all products. For example I would like to show the following where Widget b and Battery are filtered out on the visual filter, but the total orders still shows 9 so my Occerance % is the same and doesn't re-calcuate based on the visible products. 

 

producthow many timestotal items ordered% on order 
Widget b4944% 
Widget a2922% 
Accessory a2922% 
Battery1911% 

 

now if I use a filter to just select say 4/15 I would expect the result to be like this

producthow many timestotal items ordered% on order
Widget b2450%
Widget a1425%
Accessory a1425%
Battery040%

 

I've been trying to use the ALLEXCEPT but it's not working the way I thought it would. 

total orders =
CALCULATE(DISTINCTCOUNT(ordertable[order_number]), ALLEXCEPT(ordertable, ordertable[date]))
 
how do I count the total when I have a visual filter, so that I ignore the visual filter and get the total of 9 in this example?
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous,

     

    Accoriding to my understanding, you want to get dynamic count and percentage based on selected values in slicer(Moment and Date), right?

     

    I have done it with creating a new table using the following formula:

    NewTable = DISTINCT(SELECTCOLUMNS('Table',"Product",[Moment]))

     

    Then please try these:

    how many times = IF(MAX('NewTable'[Product]) in ALLSELECTED('Table'[Moment]),CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Moment]=MAX('NewTable'[Product]))),0)+0
    total items = CALCULATE(COUNTROWS('Table'),FILTER('NewTable','NewTable'[Product] =MAX('Table'[Moment])))
    % on order = DIVIDE([how many times],[total items])

    The final output is shown below:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

16 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    I would try it with the following measures:

    The amount of orders:

    Amount Orders = COUNTROWS( ordertable )

     

    The total orders: 

    Total Orders = CALCULATE( COUNTROWS( ordertable ), ALL( ordertable ) )

     

    And then the ocurrence as percentage:

    Occurrence = DIVIDE( [Amount Orders], [Total Orders] )

     

    This is creating the following result:

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      selimovd this is close but Ideally I would show just Accessory 1 and widget 1 on the table output. using a visual filter to exclude widget 2 and Battery. when I filter to just one or two of the products then the count is only reflecting those items and not the toal.

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        Hey Anonymous ,

         

        what do you want to be different?

        If you select 2 products the numbers adapt to these two products:

         

        The numbers for Accessory 1 and widget 1 stay the same, the total adapts to the selected products.

        Can you tell me specifically how you would like the result to be?

         

        Best regards

        Denis

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Your measure looks correct. Could it be that you have dimension tables impacting the filter context? Anyway, you can try the below measure:

    TotalOrders = CALCULATE(DISTINCTCOUNT('Table'[Order_Number]),ALL('Table'[Product]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Anonymous I took off all the extra tables from my model, now it's just the one table. it has product, order_number, and Date. 

       

      when I use the following

      TotalOrders = CALCULATE(DISTINCTCOUNT('Table'[Order_Number]),ALL('Table'[Product]))

       

      it's only counting those products that are showing, I have a filter on the product under the "filters on this visual" , then I have a slicer for the date to show a single or date range.

       

      shouldn't ALL ignore the visual filter?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous,

     

    Accoriding to my understanding, you want to get dynamic count and percentage based on selected values in slicer(Moment and Date), right?

     

    I have done it with creating a new table using the following formula:

    NewTable = DISTINCT(SELECTCOLUMNS('Table',"Product",[Moment]))

     

    Then please try these:

    how many times = IF(MAX('NewTable'[Product]) in ALLSELECTED('Table'[Moment]),CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Moment]=MAX('NewTable'[Product]))),0)+0
    total items = CALCULATE(COUNTROWS('Table'),FILTER('NewTable','NewTable'[Product] =MAX('Table'[Moment])))
    % on order = DIVIDE([how many times],[total items])

    The final output is shown below:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      so I wouldn't want the total items to change with the item filter, just with the date filter. 

       

      so for example with 4/15 and 4/16 sleceted and  Accessory a selected I would see

       

      Accessory A

      how many times = 2

      total item = 8

      • Wendeley-North's avatar
        Wendeley-North
        Icon for Resolver I rankResolver I
        5 years ago

        Not the best at this, but you could try:


        total orders = SUMX ( ALLEXCEPT(ordertable, ordertable[call_time]), DISTINCTCOUNT( ordertable[call_id] ) )