Forum Discussion

ErikHolmberg's avatar
ErikHolmberg
Frequent Visitor
1 year ago

Matrix visual to show rows without values

Hi,

 

I'm trying to create a matrix visual that shows all customers even if they don't have any sales figures. And I want it to work with a date slicer. The vision I'm trying to reach is something like 

 

Client IDBusiness area 1Business area 2Business area 3Total
Client 101000100
Client 21002000300
Client 30000
Client 4100100100300
Client 50000
...............

 

I have two data tables that I'm using for this, the first one is with sales figures. Each row represents a deal so that means that every customer might not pe represented in this table, especially when using a date slicer on the visual. The sales table looks like this 

 

Client IDAmountDateBusiness area
Client 1 1002024/12/15Business area 2
Client 21002024/11/27Business area 1
Client 22002025/01/15Business area 2
Client 41002025/01/06Business area 1
Client 41002024/12/08Business area 2
Client 41002024/01/11Business area 3
............



I also have a table that includes all customers regardless of when their bought any products, it looks something like this 

Client IDDate created
Client 1 2024/10/23
Client 22024/11/27
Client 32024/01/12
Client 42024/01/11
Client 52024/11/30
......

 

Any ideas how to get this right?

Thanks a bunch for any help!

 

Need Help

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    ErikHolmberg First create a relationship between the Client ID in the customer table and the Client ID in the sales table.

    Create a measure to sum the sales amounts

    Total Sales = SUM(Sales[Amount])

     

     

    To ensure that all customers are displayed even if they don't have sales, you need to modify the measure to handle cases where there are no sales.

    DAX
    Total Sales with Zero =
    IF(
    ISBLANK([Total Sales]),
    0,
    [Total Sales]
    )

     

    Use this measure in matrix

     

    • ErikHolmberg's avatar
      ErikHolmberg
      Frequent Visitor
      1 year ago

      Thanks for the quick answer!

      What I'm not quite getting is how I with this will be able to divide the the sales per Business Area, sorry if that wasn't clear in the original post

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi ErikHolmberg 

    The formula is as simple as

    [Total Sales] + 0

    But make sure you use  the column Client dimension table as if you use the one from your fact table and there isn't a row for that client within the range selected, that client will not appear even if your rewrite your formula to return 0 if sales value is blank.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, ErikHolmberg 

    May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.


    If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.