Forum Discussion
Dynamic Field headings for matrix reports
- 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
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