Forum Discussion

JoseTomas's avatar
JoseTomas
Frequent Visitor
2 years ago
Solved

Finance Result on Matrix Visualization / DAX MEASURES /

Hi guys! I need your help with a measure...

This is my goal,  creating a  Finance report on a Matrix visualization, for example...

Total Revenue          $1000
     Revenue1           $500
          Revenue1.1   $300
          Revenue1.2   $200
     Revenue2           $500
          Revenue2.1   $400
          Revenue2.2   $100

Currently my data is like this...
Item; Key; Total
Total Revenue ;1; $1000
Revenue1;2 ;$500
Revenue1.1;3;$300
Revenue1.2;4;$200
Revenue2;5;$500
Revenue2.1;6;$400
Revenue2.2;7;$100


Also I have my structure with the same key.

My problem is this: If I make a new measure using Sum(total) and then insert it into a matrix visualition, this results in the Total Revenue and its sub-items appearing duplicated. Could someone help me resolve this issue?

Regards!!

 




                  




 

  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    Your data source has redundant data. I'd recommend removing the extra rows and adding a grouping column so that it looks like this:

     

    Sample M you can past into a new blank query:

    let
  Source = Table.FromRows(
    {
      {"Total Revenue", 1, 1000}, 
      {"Revenue 1", 2, 500}, 
      {"Revenue 1.1", 3, 300}, 
      {"Revenue 1.2", 4, 200}, 
      {"Revenue 2", 5, 500}, 
      {"Revenue 2.1", 6, 400}, 
      {"Revenue 2.2", 7, 100}
    }, 
    type table [Item = text, Key = number, Total = number]
  ),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Item], ".")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Group", each Text.BeforeDelimiter([Item], "."), type text)
in
  #"Added Custom"

     

    Without the redundant information, everything sums up nicely.

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    Your data source has redundant data. I'd recommend removing the extra rows and adding a grouping column so that it looks like this:

     

    Sample M you can past into a new blank query:

    let
  Source = Table.FromRows(
    {
      {"Total Revenue", 1, 1000}, 
      {"Revenue 1", 2, 500}, 
      {"Revenue 1.1", 3, 300}, 
      {"Revenue 1.2", 4, 200}, 
      {"Revenue 2", 5, 500}, 
      {"Revenue 2.1", 6, 400}, 
      {"Revenue 2.2", 7, 100}
    }, 
    type table [Item = text, Key = number, Total = number]
  ),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Item], ".")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Group", each Text.BeforeDelimiter([Item], "."), type text)
in
  #"Added Custom"

     

    Without the redundant information, everything sums up nicely.

  • JoseTomas's avatar
    JoseTomas
    Frequent Visitor
    2 years ago

    Hi Alexis!
    thank you for your answer! I tried it and is totally useful 🙂

    Best Regards