Forum Discussion
WhyNotLogical
3 years agoFrequent 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 c...
- 2 years ago
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 vResultThe 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
lbendlin
Super User
3 years agoIt 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.