CountIf Dax Function- How to Fix it?

[MichaelaMul]

Hi everyone,

I have a formula that says whether a product is at risk based on the following measure. I would like to make a new measure that I could put in a card to measure the total number of products at risk for a specific brand, however I have tried a few ways and it isn't showing correctly. I would have slicers that although me to filter by time and geography.

Here's the current formula that says if it is at risk or not:

Vol - Risk Level (Bottom 20%) = if((CALCULATE([Volume per $MM Rank])/[# of Products Volume])>0.80,"AT RISK", "--")

And here are the other measures that go with it:

Volume per $MM Rank = 
IF (
    NOT ( ISBLANK ( Distribution[Total Volume per $MM per item] ) ),
    RANKX (
        FILTER ( ALLSELECTED ( Distribution[Product] ), NOT ( ISBLANK ( [Total Volume per $MM per item] ) ) ),
        [Total Volume per $MM per item]
    )
)
# of Products Volume = CALCULATE(DISTINCTCOUNT(Distribution[Product]),ALL(Distribution[Product]),Distribution[ACV Weighted Distribution] > 2.5)

This is the formula that I have tried and it isn't working.

COUNT RISK = CALCULATE(COUNTA(Distribution[Product]), filter(ALLSELECTED('Distribution'),'Distribution'[Calculate Risk Volume]= 1))

I think I need to make the Risk Level Calculation into a Column but when I do that, it doesn't work, this is the column measurement I made:

Column = COUNTX
(
FILTER
(
VALUES
( 'Distribution'[Product]), CALCULATE([Volume per $MM Rank]/[# of Products Volume])>0.8),'Distribution'[Product])

Also below is sample data, along with the results when I added the Column calculation.

Geography	Time	Product	Volume per $MM per Item	ACV Weighted Distribution	SEGMENT	BRAND	SIZE	Column
STORE K	Latest 4 Weeks	BRAND ABC OAT ALMOND SWEETENED ORIGINAL PLANT A/O 52 OZ	0.09	87.09	ALMOND	BRAND ABC	52 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC OAT ALMOND SWEETENED ORIGINAL PLANT A/O 52 OZ	0.27	89.72	ALMOND	BRAND ABC	52 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED EXTRA CREAMY PLANT FULL 64 OZ	0.36	68.47	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED EXTRA CREAMY PLANT FULL 64 OZ	0.12	64.91	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED REDUCED SUGAR VANILLA PLANT A/O 64 OZ	0.20	93.33	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED REDUCED SUGAR VANILLA PLANT A/O 64 OZ	0.65	93.67	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED ORIGINAL PLANT A/O 64 OZ	1.50	99.25	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED ORIGINAL PLANT A/O 64 OZ	0.46	99.09	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND UNSWEETENED ORIGINAL PLANT A/O 64 OZ	0.86	99.04	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND UNSWEETENED ORIGINAL PLANT A/O 64 OZ	2.74	99.20	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED BANANA PLANT A/O 64 OZ	0.52	90.13	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED BANANA PLANT A/O 64 OZ	0.16	89.82	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND UNSWEETENED VANILLA PLANT A/O 64 OZ	2.34	98.99	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND UNSWEETENED VANILLA PLANT A/O 64 OZ	0.74	98.88	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED HONEY VANILLA PLANT A/O 64 OZ	0.32	89.07	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED HONEY VANILLA PLANT A/O 64 OZ	1.02	89.40	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED VANILLA PLANT A/O 64 OZ	1.42	99.06	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED VANILLA PLANT A/O 64 OZ	0.43	98.70	ALMOND	BRAND ABC	64 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND UNSWEETENED VANILLA PLANT A/O 96 OZ	1.78	96.39	ALMOND	BRAND ABC	96 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND UNSWEETENED VANILLA PLANT A/O 96 OZ	0.52	96.15	ALMOND	BRAND ABC	96 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED ORIGINAL PLANT A/O 96 OZ	0.26	91.13	ALMOND	BRAND ABC	96 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED ORIGINAL PLANT A/O 96 OZ	0.90	91.98	ALMOND	BRAND ABC	96 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND SWEETENED VANILLA PLANT A/O 96 OZ	1.41	93.36	ALMOND	BRAND ABC	96 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND SWEETENED VANILLA PLANT A/O 96 OZ	0.41	92.86	ALMOND	BRAND ABC	96 OZ	1
STORE K	Latest 4 Weeks	BRAND ABC ALMOND UNSWEETENED ORIGINAL PLANT A/O 96 OZ	0.66	96.89	ALMOND	BRAND ABC	96 OZ	1
STORE K	Latest 13 Weeks	BRAND ABC ALMOND UNSWEETENED ORIGINAL PLANT A/O 96 OZ	2.24	97.43	ALMOND	BRAND ABC	96 OZ	1

[Anonymous]

Hi @MichaelaMul ,

Based on the information you have provided, can you provide your desired output. This would allow us to better reproduce and solve the problem you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[MichaelaMul]

I was able to make this measure work, however I'm wondering if there is a workaround so that I don't need to make an individual one for each brand I want a card for.  For example, I would want to look at BRAND DEF for a Cashew Category. When I didn't include the filter and filtered it separately, it changed the # of products, because it was filtered by the brand instead of the segment, and as a result changed the number of products at risk. 

Count Risk Vol = 
COUNTROWS(
		FILTER(
			ADDCOLUMNS(SUMMARIZECOLUMNS(
				'Distribution'[Product]),
				"risk", CALCULATE([Vol - Risk Level (Bottom 20%)])
				), 
			[Vol - Risk Level (Bottom 20%)] = "AT RISK"
			&& CONTAINSSTRING(Distribution[Product], "BRAND ABC")
	))