In Power BI Desktop visualizations, matrixes are often used. Mostly the matrix calculations will be involved.
Scenarios:
About how to switch subtotal and how to switch subtotal at different levels.
Procedures:
Select this matric visual, click on the small brush pattern under the visual image – Format, next click Subtotal, turn off the corresponding row subtotal or column subtotal as appropriate. This will turn on or off subtotal for all levels.
In this case, you can also choose to turn off the subtotal for different levels. At this point we need to create a Measure formula, using the ISINSCOPE function.
Attention: To achieve this, the subtotal above needs to remain open.
About ISINSCOPE: Return true when the specified column is the level in a hierarchy of levels.
More details: ISINSCOPE
Sample data:
We need to hide the total of Assurance under the level A1 but still show the total of AA1.
Firstly, we can create a Measure as follows.
target_measure =
IF (
ISINSCOPE ( Employees[StaffName] ),
SUM ( Employees[Target] ),
IF (
ISINSCOPE ( Employees[StaffOfficeName] ),
SUM ( Employees[Target] ),
IF (
ISINSCOPE ( Employees[StaffUnitName] ),
IF (
MAX ( Employees[StaffUnitName] ) = "Assurance",
BLANK (),
SUM ( Employees[Target] )
),
IF ( ISINSCOPE ( Employees[StaffPosition] ), SUM ( Employees[Target] ) )
)
)
)
In this formula, we should first find Assurance is under the level of A1, that is, in the StaffUnitName column, other levels do not change, in this level if this column has the value = Assurance, then itwill output blank, or you can output the value to text instead of blank.
Then the rest will output sum (target). Similarly, if you want the subtotal of a field under other levels not to be displayed, find the corresponding Level, and get the corresponding result by using the if function and ISINSCOPE function.
Scenarios:
We can display the subtotal as SUBTRACTION instead of SUM of the values.
Sample data:
For example, the expected output of total is -6, 2, 2, -6, respectively, at this time we can control the value of total by creating a measure.
At this point, we can use the HASONEVALUE function. About HASONEVALUE: Return TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.
More details: HASONEVALUE
Then create a measure like the following.
hasonevalue =
VAR _aa1 =
CALCULATE (
SUM ( Employees[Target] ),
FILTER (
ALL ( Employees ),
Employees[StaffUnitName] = "AA1"
&& Employees[StaffOfficeName] = SELECTEDVALUE ( Employees[StaffOfficeName] )
)
)
VAR _ASSURANCE =
CALCULATE (
SUM ( Employees[Target] ),
FILTER (
ALL ( Employees ),
Employees[StaffUnitName] = "Assurance"
&& Employees[StaffOfficeName] = SELECTEDVALUE ( Employees[StaffOfficeName] )
)
)
RETURN
IF (
HASONEVALUE (Employees[StaffName] ),
MAX ( Employees[Target] ),
_aa1 - _ASSURANCE
)
If the context of Employees[StaffName] is filtered to a different value, the value in the dataset is returned, otherwise the corresponding value can be obtained by calculation based on the desired output.
Summarize:
Through the above study, I believe you can skillfully use the matrix.
Author: Polly Pu
Reviewer: Kerry Wang & Ula Huang
Be the first to comment