Forum Discussion

PWRBI's avatar
PWRBI
Frequent Visitor
3 years ago
Solved

Countrows with Sumx not working

Hi guys,
I have the following data

 

StorageBoxpackageweight
AA1A10130
AA1A10220
AA2A201

40

AA2A202

30

 

Storage A has a capacity of 70. Now I would like to display in an additional table how many boxes in warehouse A still have free capacity. In the above example, this would be box A1 because 20 are still free, A2 is full with 70.

 

StorageFree boxes
A1

 

This is my Measure:

 

MEASURE =

VAR VAR1 = CALCULATE( SUMX( SUMMARIZE('STORAGE', STORAGE[STORAGE], STORAGE[BOX], "SCORE", sum(STORAGE[WEIGHT]), [SCORE] ))
VAR VAR2 = COUNTROWS(FILTER(DISTINCT(STORAGE[BOX]), VAR1< 70))

return VAR2

 

It works when I look at the box level, but when I want the total across all Storages, nothing shows up, so the total row doesn't match. What do I have to change?

  • Hi PWRBI 
    Please try

    MEASUR =
    SUMX (
        VALUES ( STORAGE[BOX] ),
        INT ( CALCULATE ( SUM ( TORAGE[WEIGHT] ) ) < 70 )
    )

3 Replies