Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I would like to create a table visual that looks like this:
Category | This Month | Last Month |
A (USD) | 10 | 9 |
A (EUR) | 9.2 | 8.28 |
B (USD) | 20 | 19 |
B (EUR) | 18.4 | 17.48 |
C (USD) | 30 | 29 |
C (EUR) | 27.6 | 26.68 |
D (USD) | 5 | 4 |
D (EUR) | 4.6 | 3.68 |
In my model I have a table of categories, a table of dates, and a table of prices with USD and EUR. All I have been able to get is the following:
Category | This Month | Last Month | ||
USD | EUR | USD | EUR | |
A | 10 | 9.2 | 9 | 8.28 |
B | 20 | 18.4 | 19 | 17.48 |
C | 30 | 27.6 | 29 | 26.68 |
D | 5 | 4.6 | 4 | 3.68 |
Is there a way to achieve the first table?
Thanks in advance!
Solved! Go to Solution.
Hi,
Please take following steps:
1)Create a calculated table:
Table 2 =
DISTINCT (
SELECTCOLUMNS (
'Table',
"Category", 'Table'[Category] & "(" & 'Table'[Currency] & ")",
"C", 'Table'[Category],
"Currency", 'Table'[Currency]
)
)
2)Create these two columns:
This Month =
CALCULATE (
SUM ( 'Table'[Prices] ),
FILTER (
'Table',
'Table'[Category] = EARLIER ( 'Table 2'[C] )
&& 'Table'[Currency] = EARLIER ( 'Table 2'[Currency] )
&& 'Table'[Month] = "This Month"
)
)
Last Month =
CALCULATE (
SUM ( 'Table'[Prices] ),
FILTER (
'Table',
'Table'[Category] = EARLIER ( 'Table 2'[C] )
&& 'Table'[Currency] = EARLIER ( 'Table 2'[Currency] )
&& 'Table'[Month] = "Last Month"
)
)
3)The result shows:
See my attached pbix file.
Best Regards,
Giotto
Hi,
Please take following steps:
1)Create a calculated table:
Table 2 =
DISTINCT (
SELECTCOLUMNS (
'Table',
"Category", 'Table'[Category] & "(" & 'Table'[Currency] & ")",
"C", 'Table'[Category],
"Currency", 'Table'[Currency]
)
)
2)Create these two columns:
This Month =
CALCULATE (
SUM ( 'Table'[Prices] ),
FILTER (
'Table',
'Table'[Category] = EARLIER ( 'Table 2'[C] )
&& 'Table'[Currency] = EARLIER ( 'Table 2'[Currency] )
&& 'Table'[Month] = "This Month"
)
)
Last Month =
CALCULATE (
SUM ( 'Table'[Prices] ),
FILTER (
'Table',
'Table'[Category] = EARLIER ( 'Table 2'[C] )
&& 'Table'[Currency] = EARLIER ( 'Table 2'[Currency] )
&& 'Table'[Month] = "Last Month"
)
)
3)The result shows:
See my attached pbix file.
Best Regards,
Giotto
@Anonymous
Try this in Advanced Editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcvLDcAwCAPQXThHqBBK6LGfLVD2X6M4OdjIeiKTbmokR9XFiq4Ea9BsSU8NhUmw4UBlsG1+a3WwDnYcsDr75q/WWbGF+O+L5g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, #"This Month USD" = _t, #"This Month EUR" = _t, #"Last Month USD" = _t, #"Last Month EUR" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"This Month USD", Int64.Type}, {"This Month EUR", type number}, {"Last Month USD", Int64.Type}, {"Last Month EUR", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Category1", each [Category]&"-"&[Attribute.2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute.2", "Category"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.1]), "Attribute.1", "Value", List.Sum)
in
#"Pivoted Column"
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
@Anonymous , the first table looks like a table visual.
In case you have category and currency in the same table Create a combined column
New Cat = [Category] & "(" & [currency] & ")" and use that as a column in visual
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |