Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following matrix table with square unichar icons as data and numerical totals.When the total is derived by adding more than one value from the matrix all is fine. However, when the total comes from a single value then the total presents as a square icon. Here is a picture to demonstate the problem:
As you can see, the totals for Team 4 and Stage 4 are icons. I would like these to present as numbers. Here is what the data looks like:
ProjectTeamTypeStage
Project 1 | Team 1 | Type 1 | Stage 1 |
Project 13 | Team 1 | Type 3 | Stage 2 |
Project 14 | Team 2 | Type 2 | Stage 1 |
Project 15 | Team 2 | Type 2 | Stage 1 |
Project 16 | Team 3 | Type 2 | Stage 1 |
Project 17 | Team 4 | Type 2 | Stage 1 |
Project 2 | Team 1 | Type 2 | Stage 2 |
Project 3 | Team 1 | Type 3 | Stage 3 |
Project 4 | Team 2 | Type 1 | Stage 4 |
Project 5 | Team 2 | Type 2 | Stage 1 |
Project 6 | Team 2 | Type 3 | Stage 2 |
Project 7 | Team 3 | Type 1 | Stage 3 |
Project 9 | Team 3 | Type 3 | Stage 1 |
And this is the measure I have used for the data values:
square =
VAR _type1 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 1" )
)
VAR _type2 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 2" )
)
VAR _type3 =
CALCULATE (
COUNT ( 'Table'[Type] ),
FILTER ( 'Table', 'Table'[Type] = "Type 3" )
)
VAR _quare =
REPT ( UNICHAR ( "128998" ), _type1 ) & REPT ( UNICHAR ( "128997" ), _type2 )
& REPT ( UNICHAR ( "129000" ), _type3 )
RETURN
IF (
HASONEVALUE ( 'Table'[Stage] ),
IF (
HASONEVALUE ( 'Table'[Team] ),
_quare,
FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
),
FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
)
Here is a link to the pbix file if that makes it easier:
Any help would be greatly appreciated!
Basically I have not yet seen a reliable method to check if one is on a row in a matrix or in the total row. If there are many rows in the matrix, you can always check if one value is visible or many (using HASONEVALUE, for instance). If one, then you know you're on an individual row. If many, you know it's the total. But a real problem arises if only one row is visible, or has been selected via a slicer, in the matrix. Then there's no way to distinguish between the two according to my knowledge.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |