Forum Discussion

Aditi_Saxena's avatar
Aditi_Saxena
Frequent Visitor
3 years ago

SUMIF and COUNTIF

I have data of different products and their sale. The desired result is to first sum the items sold based on the product. Thereafter, count the number of products which 0 total items sold. 
The measure that I applied counts the products that have 0 items sold at any point while I want to count the products only if their total item sold is 0.

Also, I want to create a bar graph where different counts are represented as bars (Image 2).

Please find below the picture for your reference:

 

Image 2

 

Need Help

2 Replies

  • mlsx4's avatar
    mlsx4
    Memorable Member
    3 years ago

    Hi Aditi_Saxena 

     

    Your formulas should be:

    sumProducts = SUM('Table'[Items sold])
    Products with no sell = 
COUNTROWS(
    FILTER(
        SUMMARIZE( 'Table', 'Table'[Products] ),
        CALCULATE( [sumProducts] ) = 0
    )
)

     

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hi, Aditi_Saxena 

     

     

    1. Create a measure to sum the items sold per product:

       
      Total Sales = SUM('Table'[items_sold])

    2. Create a measure to count the number of products with zero total items sold. This could be done with the CALCULATE and FILTER functions:

       
      Zero Sales Count = CALCULATE( COUNT('Table'[product]), FILTER( ALL('Table'), [Total Sales] = 0 ) )

    3. To visualize the counts of products with zero and non-zero sales in a bar chart:

      a. Create a new calculated table: Sales Summary = UNION( SUMMARIZE(ALL('Table'), "Sales", "Zero Sales", [Zero Sales Count]), ROW("Sales", "Non-Zero Sales", COUNT('Table'[product]) - [Zero Sales Count]) )

      b. Create a bar chart using this new table Sales Summary as the source. Drag the Sales field to the Axis area and Count field to the Values area.

    Please make sure to replace 'Table' with your actual table name, and 'product' and 'items_sold' with your actual column names for the products and items sold respectively.

    This approach first calculates the total sales for each product and then calculates the count of products with zero total sales. A new calculated table is created to represent the counts of zero and non-zero sales, which is used to create a bar chart.