Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Count of Product Category if contains sales

  • 1.  Count of Product Category if contains sales

    Bronze Contributor
    Posted 2 days ago
    I have a Matrix with Customers, Products, and Sales. I want to create a column that counts each product category a customer has purchased from and returns a total.

    For example, the customer on line 1 has purchased from 3 product categories (indicated by there being sales in the cell). I want my total column to show "3".



    I know there's not a COUNTIF function in BI and I have read the workarounds, but can't find any that fit this scenario.

    ------------------------------
    Mary Moore
    Sales Operations Analyst
    6153248429
    ------------------------------


  • 2.  RE: Count of Product Category if contains sales

    Top Contributor
    Posted 2 days ago
      |   view attached
    Hi @Mary Moore,
    Considering that the Matrix is your Data source (not a power bi visual) you can get the count of products by using Power Query.​
    I followed these steps
    1. Create the "Source" table
      1. Load the Data (matrix)
    2. Create the "Total" table
      1. Reference the Source table
      2. Unpivot the data
      3. Add a custom column "# Sales" that return 1 if [Value] <> 0
      4. Remove the "Products" and "Value" table
      5. Group by [Customers] and sum [# Sales]
    3. Create the "Merged" table
      1. Merge the Source and the Total tables
      2. Expend only the (#Sales] field and voilà!

    The solution is attached to this reply. Let me know if this solved your problem.

    ------------------------------
    Best regards
    Mehdi HAMMADI
    MCT | MCSA BI Reporting | MCSE Data Management and Analytics
    2019 Ruby Award Dynamic Communities
    ------------------------------