Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
WhyNotLogical
Frequent Visitor

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 chose a comparator. A variance is then generated by the calculation Version 1 - Version 2. So far so easy. However I wish to dynamically change the fied names of the calculations (V1 repersenets Version1, V2 represents Version2). I can manually rename the field headers (which is pointless as they are still static - so any change to the versions would not be represented). I have a formula that correctly derives the titles but it seems I can not use a formula to update the headers (which from my point of view is absolutely basic functionality). Is there any way around this issue? Please see below screenshot for details (real data has been replaced by a representation). Any help would be greatly appreciated.
PowerBI eg.png

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

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

 

DataInsights_0-1715728900455.png

 

Result:

 

DataInsights_1-1715728960203.png

 

-----

 

DataInsights_2-1715728981326.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

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

 

DataInsights_0-1715728900455.png

 

Result:

 

DataInsights_1-1715728960203.png

 

-----

 

DataInsights_2-1715728981326.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




lbendlin
Super User
Super User

It might be better to teach your users how to personalize visuals, how to use the filter pane (where all your buttons belong) and how to use Analyze in Excel. Otherwise you're looking at a maintenance nightmare.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.