COUNT and SUM only if has no zeros

[fabnishi1207]

Hi guys,

I need a dax measure to show if STATE has achieved 100% of delivery for product A,B,C

My column "Delivered" is an calculated measure. 1 means delivered and 0 means failed. and I need a new column on the state level.

I have the table below:

DATE	STATE	PRODUCT	DELIVERED (DAX MEASURE)
27-Jul	NSW	A	1
27-Jul	NSW	B	1
27-Jul	NSW	C	0
27-Jul	VIC	A	1
27-Jul	VIC	B	1
27-Jul	VIC	C	1

I need this new dax measure showing VIC as 1 (achieved) and NSW as 0 (failed)  and ten sum all the values with 1.

STATE	NEW DAX MEASURE
NSW	0
VIC	1

thanks in advance

[Icey · Accepted solution]

Hi @fabnishi1207 ,

Try this:

Measure =
SUMX (
    ADDCOLUMNS (
        'Table',
        "NEW DAX MEASURE", SWITCH ( 'Table'[STATE], "VIC", 1, "NSW", 0 )
    ),
    [NEW DAX MEASURE]
)

Best Regards,

Icey

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

[AlB]

Hi @fabnishi1207 

Assuming Delivered is a calculated column in the your table,  you could:

1. Place Table1[State] in the rows of a table visual

2. Create this measure and place it in the visual

NewMeasure3 = 
SUMX (
    DISTINCT ( Table3[State] ),
    IF ( CALCULATE ( COUNT ( Table3[Product] ), Table3[Delivered] = 0 ) > 0, 0, 1 )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[BI_Jo]

Hi
 
You could create a new table using 'group by' in Power Query Editor:

And then create a measure on the table:

Measure = if(sum('Table'[SUM]) = sum('Table'[Count]),"Achieved","Failed")

Which will give you this:

Jo