DAX Calculation Help with filters on visual

[Anonymous]

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

Moment	call_id	call_time
Widget b	4509105517166590	4/15/2021
Widget a	4505281519878140	4/15/2021
Widget b	4505281519878140	4/15/2021
Accessory a	4825164887556090	4/15/2021
Widget a	4510485208301560	4/16/2021
Widget b	4510485208301560	4/16/2021
Accessory a	4510281969106940	4/16/2021
Widget b	4510281969106940	4/16/2021
Battery	4523966070456320	4/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. 

product	how many times	total items ordered	% on order
Widget b	4	9	44%
Widget a	2	9	22%
Accessory a	2	9	22%
Battery	1	9	11%

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

product	how many times	total items ordered	% on order
Widget b	2	4	50%
Widget a	1	4	25%
Accessory a	1	4	25%
Battery	0	4	0%

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?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-eqin-msft · Accepted solution]

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]

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]

Not the best at this, but you could try:

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

[gdarakji]

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]

@gdarakji 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?

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@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]

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]

@selimovd 

so when I use CALCULATE( COUNTROWS( ordertable ), ALL( ordertable ) )

I get the count of rows on the total table, however it's not working when I apply a date slicer to the data to look at an indiviual day. 

[Anonymous]

@selimovd @gdarakji 

Updated the original post to include date since that seems to be the issue. 

[selimovd]

Hey @Anonymous ,

please explain detailed what you want.

Show us your data model, tell us what are you filtering and what do you expect the result to be.

Make it as easy as possible for us to help you, we cannot know what your scenario is when you don't describe it properly...

Best regards

Denis

[Anonymous]

Thanks @selimovd  for all the help, I just updated the main post. I think that added date context should help. 

Moment	call_id	call_time
Widget b	4509105517166590	4/15/2021
Widget a	4505281519878140	4/15/2021
Widget b	4505281519878140	4/15/2021
Accessory a	4825164887556090	4/15/2021
Widget a	4510485208301560	4/16/2021
Widget b	4510485208301560	4/16/2021
Accessory a	4510281969106940	4/16/2021
Widget b	4510281969106940	4/16/2021
Battery	4523966070456320	4/17/2021

should show the following with a slicer on date for 4/15

product	how many times	total items ordered	% on order
Widget b	2	4	50%
Widget a	1	4	25%
Accessory a	1	4	25%
Battery	0	4	0%

[selimovd]

Hey @Anonymous ,

no, it doesn't. I still don't know the relationship and what you expect as a result.

Please add that.

Best regards

Denis

[Anonymous]

@selimovd the table is very simple, just product, order_id and date. 

I need to be able to look at the total count of items, count by indiviual item, and filter than by date with a slicer. 

[selimovd]

@Anonymous  how is the relationships with these tables?

Can you share the file?