Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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...
  • v-gizhi-msft's avatar
    6 years ago

    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