DAX - Count Distinct - Filtering with Numeric Parameter on 2 visuals

[Specialist707]

Hi,

I'm trying to perform a shrink analysis where I have a number of SKUs per user. Each user can record a positive adjustment, a negative adjustment, or no adjustment, which when totalled, gives a net adjustment. I would like to apply a numeric parameter that allows me to filter out SKUs that are above a threshold in a matrix chart, as well as have another chart that shows a distinct count of SKUs where the net of the positive+negative adjustments are less than the parameter per SKU

My data set looks like this:

User	SKU	Net Dollars	Adjustment Type
AA	1	-50	Negative
AA	2	-100	Negative
AA	3	100	Positive Adjustment
AA	1	50	Positive Adjustment
AA	2	-500	Negative
AA	3	100	Positive Adjustment
AA	1	0	No Adjustment
AA	2	6	Positive Adjustment
AA	3	25	Positive Adjustment
AA	1	50	Positive Adjustment
AA	2	-100	Negative
AA	3	0	No Adjustment
AA	4	30	Positive Adjustment
AA	5	0	No Adjustment

When Pivotted, it looks like this:

1) I would like to remove the items with only a 0 in the no adjustment column (e.g, SKU 5 would go away), or in other words, anything that nets out to 0

2) I would like to have a numeric parameter where if i set it to 250, then the -694 (SKU 2 would go away). I presume an ABS function is needed here.

3) I would then like to have a separate bar chart that would show the number of resulting SKU's from steps 1 & 2. So ideally, with the parameter set to 650, it would show User "AA" has 3 SKUs (1, 3 & 4).

I've tried numerous things like having a formula like the following, and making it a filter on the visual where the filter is 1:

SKU Filter= if(abs(CALCULATE(sum('Shrink Data'[NET_DOLLARS]),'Shrink Data'[Adjustment Type]="Negative Adjustment") + CALCULATE(sum('Shrink Data'[NET_DOLLARS]),'Shrink Data'[Adjustment Type]="Positive Adjustment"))<[PrmOffsetAmount Value],1,0)

That seems to filter the data to work with the parameter. I can then remove the 0's by doing the following, and setting a visual filter to is 1:

Remove 0 = IF(CALCULATE(sum('Shrink Data'[NET_DOLLARS]),'Shrink Data'[Adjustment Type]="Negative Adjustment") + CALCULATE(sum('Shrink Data'[NET_DOLLARS]),'Shrink Data'[Adjustment Type]="Positive Adjustment")=0, 0,1)

Where i'm getting caught up is then creating another chart that shows the resulting user and the number of SKUs (Distinct Count) left from the filtering & parameter filter.

I greatly appreciate any help the community can provide!

[Anonymous · Accepted solution]

HI @Specialist707,

#1, These zero value records are really stored in your table, you can set a filter to exclude them, but these row/column will keep displayed on the matrix excepted full row/column has blank values.(power bi will auto hide the row or column with blank values)

#2, It sounds like you want to filter on the summary value instead of detail records. For this scenario, I'd like to suggest you modify your formula to add a variable to summary table records at first, then you can check the current category and summarize variable table values to filter records.

All the secrets of SUMMARIZE - SQLBI

#3, You can modify the above formula with selectedvalue to interaction with what if parameter.

Use what-if parameters to visualize variables - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng