Forum Discussion

aashudream's avatar
aashudream
Regular Visitor
4 years ago
Solved

Months and Quarters side by side

I would like to see Months and Quarters side by side and not have to drill down in the visual.. any suggestion?

 

Month and values changed as per selection for last 12 month.

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi aashudream ,

    According to your description, if the data you showed above is a matrix, Indicates that in the date column, all dates are in the same column as the quarters, this requires create a new table.

    Table 2 = UNION(VALUES('Table'[Date]),{"Q1","Q2","Q3","Q4"})

    Then create three measures.

    GSV =
VAR _A =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Global category] = MAX ( 'Table'[Global category] )
                && FORMAT ( 'Table'[Date], "M/D/YYYY" ) = MAX ( 'Table 2'[Date] )
        ),
        'Table'[GSV]
    )
RETURN
    SWITCH (
        MAX ( 'Table 2'[Date] ),
        "Q1",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[GSV]
            ),
        "Q2",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q2"
                ),
                'Table'[GSV]
            ),
        "Q3",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q3"
                ),
                'Table'[GSV]
            ),
        "Q4",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[GSV]
            ),
        _A
    )

    Net Sales =
VAR _A =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Global category] = MAX ( 'Table'[Global category] )
                && FORMAT ( 'Table'[Date], "M/D/YYYY" ) = MAX ( 'Table 2'[Date] )
        ),
        'Table'[Net Sales]
    )
RETURN
    SWITCH (
        MAX ( 'Table 2'[Date] ),
        "Q1",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Net Sales]
            ),
        "Q2",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q2"
                ),
                'Table'[Net Sales]
            ),
        "Q3",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q3"
                ),
                'Table'[Net Sales]
            ),
        "Q4",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Net Sales]
            ),
        _A
    )

    Gross Profit =
VAR _A =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Global category] = MAX ( 'Table'[Global category] )
                && FORMAT ( 'Table'[Date], "M/D/YYYY" ) = MAX ( 'Table 2'[Date] )
        ),
        'Table'[Gross Profit]
    )
RETURN
    SWITCH (
        MAX ( 'Table 2'[Date] ),
        "Q1",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Gross Profit]
            ),
        "Q2",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q2"
                ),
                'Table'[Gross Profit]
            ),
        "Q3",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q3"
                ),
                'Table'[Gross Profit]
            ),
        "Q4",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Gross Profit]
            ),
        _A
    )

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi aashudream 

     

    If you want to have Months and then Quarters as per the table you've presented, you have a few options, all of which will likely require a little bit of work:

    • You can set up your source data to be structured in the format you want.  Repeatability might be an issue if you are extracting raw data out of a system.
    • You can set up the structure you want in Power Query using the source data.
    • You can set up the structure you want in canvas view using dax measures for the Current Month, Last Month, etc., back to 12 months and repeat the same process for the Quarterly periods.  Then you can add these in order into your values on the Matrix.

    I am not sure that there are other ways. There might be but to the best of my knowledge, I am not aware of any.  Note, if you are intending on doing this with various components of your data (e.g. you may want to work out Net Assets, Gross Profit, Net Profit, etc...) I recommend using Calculation Groups (https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallproducts-allversions) as you will only need to do this once rather than doing it for each item.

     

    Hope this makes sense! Let me know if you have any questions.


    Theo

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi aashudream ,

    According to your description, if the data you showed above is a matrix, Indicates that in the date column, all dates are in the same column as the quarters, this requires create a new table.

    Table 2 = UNION(VALUES('Table'[Date]),{"Q1","Q2","Q3","Q4"})

    Then create three measures.

    GSV =
VAR _A =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Global category] = MAX ( 'Table'[Global category] )
                && FORMAT ( 'Table'[Date], "M/D/YYYY" ) = MAX ( 'Table 2'[Date] )
        ),
        'Table'[GSV]
    )
RETURN
    SWITCH (
        MAX ( 'Table 2'[Date] ),
        "Q1",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[GSV]
            ),
        "Q2",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q2"
                ),
                'Table'[GSV]
            ),
        "Q3",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q3"
                ),
                'Table'[GSV]
            ),
        "Q4",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[GSV]
            ),
        _A
    )

    Net Sales =
VAR _A =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Global category] = MAX ( 'Table'[Global category] )
                && FORMAT ( 'Table'[Date], "M/D/YYYY" ) = MAX ( 'Table 2'[Date] )
        ),
        'Table'[Net Sales]
    )
RETURN
    SWITCH (
        MAX ( 'Table 2'[Date] ),
        "Q1",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Net Sales]
            ),
        "Q2",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q2"
                ),
                'Table'[Net Sales]
            ),
        "Q3",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q3"
                ),
                'Table'[Net Sales]
            ),
        "Q4",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Net Sales]
            ),
        _A
    )

    Gross Profit =
VAR _A =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Global category] = MAX ( 'Table'[Global category] )
                && FORMAT ( 'Table'[Date], "M/D/YYYY" ) = MAX ( 'Table 2'[Date] )
        ),
        'Table'[Gross Profit]
    )
RETURN
    SWITCH (
        MAX ( 'Table 2'[Date] ),
        "Q1",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Gross Profit]
            ),
        "Q2",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q2"
                ),
                'Table'[Gross Profit]
            ),
        "Q3",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q3"
                ),
                'Table'[Gross Profit]
            ),
        "Q4",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Global category] = MAX ( 'Table'[Global category] )
                        && 'Table'[Quarter] = "Q1"
                ),
                'Table'[Gross Profit]
            ),
        _A
    )

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.