Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
Solved! Go to Solution.
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.
Hi Alexis!
thank you for your answer! I tried it and is totally useful 🙂
Best Regards
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.
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |