Forum Discussion

WhyNotLogical's avatar
WhyNotLogical
Frequent Visitor
3 years ago
Solved

Dynamic Field headings for matrix reports

I have the need to build reports with dynamic headers for field names.  The Userbase want to do variance anaylsis on various scenarios. To enable this, they chose a version to be the base and then c...
  • DataInsights's avatar
    2 years ago

    WhyNotLogical,

     

    Here's a solution that uses field parameters (see link below). Create a field parameter for each version. In your model, you would have six rows (one for each measure) in each field parameter.

     

    VersionV1 = {
        ("Actual", NAMEOF('Measure'[Actual]), 0),
        ("Budget", NAMEOF('Measure'[Budget]), 1),
        ("Forecast", NAMEOF('Measure'[Forecast]), 2)
    }
    VersionV2 = {
        ("Actual", NAMEOF('Measure'[Actual]), 0),
        ("Budget", NAMEOF('Measure'[Budget]), 1),
        ("Forecast", NAMEOF('Measure'[Forecast]), 2)
    }

     

    Create a variance measure:

     

    Variance = 
    VAR vVersion1 =
        TREATAS ( { MAX ( VersionV1[VersionV1] ) }, FactTable[Version] )
    VAR vVersion2 =
        TREATAS ( { MAX ( VersionV2[VersionV2] ) }, FactTable[Version] )
    VAR vVersion1Amount =
        CALCULATE ( [Amount], vVersion1 )
    VAR vVersion2Amount =
        CALCULATE ( [Amount], vVersion2 )
    VAR vResult = vVersion1Amount - vVersion2Amount
    RETURN
        vResult

     

    The Amount measure is a sum of your fact table amount:

     

    Amount = SUM ( FactTable[Amount] )

     

     Create a matrix with the field parameters and Variance measure:

     

     

    Result:

     

     

    -----

     

     

    https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters