Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic compare two scenarios based off selection

Creating a financial report that will have a few different scenarios (years) that will need to be compared across a few different metrics. User would come in and select which two years they want to s...
  • Anonymous's avatar
    Anonymous
    4 years ago
    entityYYYYRegionMarketClient_BillingsClient_Net_RevenueClient_Total_Gross_Revenue
    5frFRANCcon2019EMEAFrance7215.0017215.0017215.001
    5grGRBGMcon2019EMEAGreece1139903.943169980.721169980.721
    5totANNALECT2019GlobalGlobal-0.3300
    5totPUERT2019LATAMPuerto Rico000
    5xtotBELGM2019EMEABelgium686262.4829564.94429564.944
    2clCHILE2019LATAMChile0-83248.259-83248.259
    5frFRANCcon2019EMEAFrance48257.70948257.70948257.709
    5totNZLND2019APACNew Zealand000
    5xtotBELGM2019EMEABelgium490480.47935611.35135611.351
    5totOUTDR2019North AmericaUSA761721512151
    5totPHDUS2019North AmericaUSA1881256131983131983
    5totRESOL2019North AmericaUSA344556.0995790.55333039
    5totICON2019North AmericaUSA4185-78304185
    5frFRANCcon2019EMEAFrance000
    5totASTRL2019APACAustralia127774.281127774.138127774.138
    5vnVIETNcon2019APACVietnam631129.28122485.76622485.766
    5xtotBELGM2019EMEABelgium26706.153997.8193997.819
    5totOUTDR2019North AmericaUSA13907408304864304864
    5dnDNMRKcon2019EMEADenmark000
    5grGREECcon2019EMEAGreece438234.409316254.77316254.77
    5totOUTDR2019North AmericaUSA4222922004120041
    5totMOBIL2019EMEAUnited Kingdom000
    5totUKGRP2019EMEAUnited Kingdom063424.81763424.817
  • PaulDBrown's avatar
    4 years ago

    Ok, here are two possible ways of going about this.
    The first is using the default matrix capabilities; this method will have a better performance but the metrics' headers are less clear.


    The second involves creating a specific table layout which recreates the measures displayed to be used as the matrix header. It provides more clarity at the expense of performance possibly (my sample dataset is minute so I can't really compare the performances of both solutions).

     

    So...

    Option 1: Default matrix capabilities.

    We need two tables for "Year" to be able to compare the values. One of these tables has a regular One-to-many active relationship with the fact table; the second Year table's relationship is inactive. The model looks like this:

     The measures for each metric follow these patterns:
    For the first Year selection.

     

    Client Billings = 
    SUM(FTable[Client_Billings])

     

    For the comparison Year selection:

     

    Comp Billings =
    CALCULATE (
        [Client Billings],
        REMOVEFILTERS ( DYear[Year] ),
        USERELATIONSHIP ( VYear[Year], FTable[Year] )
    )
    

     

    and the % var between both years:

     

    Billings % var =
    SWITCH (
        TRUE (),
        AND ( ISBLANK ( [Client Billings] ), ISBLANK ( [Comp Billings] ) ), BLANK (),
        AND ( [Client Billings] = 0, [Comp Billings] = 0 ), 0,
        DIVIDE ( [Client Billings], [Comp Billings] ) - 1
    )
    

     

    The matrix is set up as follows, with each individual measure set in order in the values bucket:

     

     

     

    To get:

     

    Option 2: Custom Header to show the columns by years

    The setup requires creating a custom table to use as the matrix header. Firstly we need a list of the measures and their respective order. (copy and paste from Excel or use the "Enter Data" option to create it):

    We then need to add the Years from the model (Crossjoin) to create both the actual header needed in the matrix and a sorting column. This is all done in Power Query:

     Leave this table unrelated in the model. The model looks like this:

    We only need to create one measure, referencing the measures we have already created, which is the one we will use for the matrix:

     

    For Matrix =
    SWITCH (
        TRUE (),
        ISBLANK ( [Client Billings] ) && ISBLANK ( [Comp Billings] )
            && ISBLANK ( [Net Revenue] ), BLANK (),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
            MAX ( Header[Order] ) = 1
        ), FORMAT ( [Client Billings], "Currency" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
            MAX ( Header[Order] ) = 2
        ), FORMAT ( [Comp Billings], "Currency" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
            MAX ( Header[Order] ) = 3
        ), FORMAT ( [Billings % var], "Percent" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
            MAX ( Header[Order] ) = 4
        ), FORMAT ( [Gross Revenue], "Currency" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
            MAX ( Header[Order] ) = 5
        ), FORMAT ( [Comp Gross Revenue], "Currency" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
            MAX ( Header[Order] ) = 6
        ), FORMAT ( [Gross Rev % Var], "Percent" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
            MAX ( Header[Order] ) = 7
        ), FORMAT ( [Net Revenue], "Currency" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( VYear[Year] ),
            MAX ( Header[Order] ) = 8
        ), FORMAT ( [Comp Net Revenue], "Currency" ),
        AND (
            MAX ( Header[HYear] ) = SELECTEDVALUE ( DYear[Year] ),
            MAX ( Header[Order] ) = 9
        ), FORMAT ( [Net Rev % Var], "Percent" )
    )
    

     

    We then set up the Matrix with the field fromt the Header Table as the columns, whatever dimension for the rows and the [For Matrix] measure as the values:
    To get:

     

    So take your pick!

    I've attached the sample PBIX file