Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Counting Rows with no records

I'm trying to create a void report that shows records that have no sales.

 

My issue is that some records that have no sales have no records so it will be blank.Is there a better way to count Blank/no record rows and zero?

 

This is what im currently using.

 

Voids = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Data,
            Data[DIVISION ID],
            Data[Product],
            Data[Week Ending],
            "X", SUM ( Data[SALES TY] )
        ),
        OR ( [X] = 0, ISBLANK ( [X] ) )
    )
)

 

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Anonymous how do you know which records don't have sales if you have only one table? It is not very clear how you will get it. You should have seperate dimension tables with all the records so that you can find out which one doesn't exist in the data table.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I created dimensions for each tables, I still end up with incorrect calculations.

       

       

      Voids = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FactData,
            'DimStore'[ Store#],
            DimProduct[Nat_Upc],
            'Date Table'[FW Week],
            "X", SUM ( FactData[SALES TY] )
        ),
        OR ( [X] = 0, ISBLANK ( [X] ) )
    )
)


       

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Anonymous try this:

     

    Count Without Sales = 
VAR __TablesWithSales = 
ADDCOLUMNS ( 
       CROSSJOIN ( 
           VALUES ( 'Dim Store'[Store #], 
           VALUES ( 'Dim Product'[Nat_UPC],
           VALUES ( 'Date Table'[FW Week]
       ), 
       "@CountFact", CALCULATE ( COUNTROWS ( FactTable ) ) 
) 
RETURN COUNTX ( FILTER ( __TablesWithSales , [@CountFact] == BLANK ( ) ), 1 )