Forum Discussion

Aditi_Saxena's avatar
Aditi_Saxena
Frequent Visitor
3 years ago
Solved

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
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Aditi_Saxena ,

    Please try below steps:

    1. below is my test table

    Table:

    Table2:

    2. create measure with below dax formula

    Sum Item Sold =
VAR cur_pt =
    SELECTEDVALUE ( 'Table'[Product] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Product] = cur_pt )
RETURN
    SUMX ( tmp, [Items sold] )

    Measure =
VAR tmp =
    SUMMARIZE ( 'Table', 'Table'[Product], "SUM_Sold", SUM ( 'Table'[Items sold] ) )
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Column1] )
VAR _result =
    SWITCH (
        _a,
        "=0", COUNTROWS ( FILTER ( tmp, [SUM_Sold] = 0 ) ),
        "1-10",
            COUNTROWS ( FILTER ( tmp, [SUM_Sold] >= 1 && [SUM_Sold] <= 10 ) ),
        "11-20",
            COUNTROWS ( FILTER ( tmp, [SUM_Sold] >= 11 && [SUM_Sold] <= 20 ) )
    )
RETURN
    _result

    3. add a table visual and clustered column chart with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Aditi_Saxena ,

    Please try below steps:

    1. below is my test table

    Table:

    Table2:

    2. create measure with below dax formula

    Sum Item Sold =
VAR cur_pt =
    SELECTEDVALUE ( 'Table'[Product] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Product] = cur_pt )
RETURN
    SUMX ( tmp, [Items sold] )

    Measure =
VAR tmp =
    SUMMARIZE ( 'Table', 'Table'[Product], "SUM_Sold", SUM ( 'Table'[Items sold] ) )
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Column1] )
VAR _result =
    SWITCH (
        _a,
        "=0", COUNTROWS ( FILTER ( tmp, [SUM_Sold] = 0 ) ),
        "1-10",
            COUNTROWS ( FILTER ( tmp, [SUM_Sold] >= 1 && [SUM_Sold] <= 10 ) ),
        "11-20",
            COUNTROWS ( FILTER ( tmp, [SUM_Sold] >= 11 && [SUM_Sold] <= 20 ) )
    )
RETURN
    _result

    3. add a table visual and clustered column chart with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Aditi_Saxena's avatar
      Aditi_Saxena
      Frequent Visitor
      3 years ago

      I tried this with DAX for two different columns; one containing whole numbers while other containing decimal numbers. It works fine with whole number but in case of decimal numbers, the "_result" calculated by "Measure" is blank. No value is being populated for any category (=0, 1-10, 11-20).
      Could you please suggest what should be changed.