Show table with two columns as table with two rows per category

[Anonymous]

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!

[v-gizhi-msft · Accepted 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

[nandukrishnavs]

@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 🙂

[amitchandak]

@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