cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stevenmcginnis
Frequent Visitor

Creating a pivot table with icons in place of numbers.

I am attempting to create a "portfolio at a glance" visualization. Essentially, what I am trying to create is a pivot table with a count of square icons instead of numbers for each intersection between "Team" & "Stage" and colored by a "Value" category, like below:

 

Desired outcome.Desired outcome.

 

I have gotten as far as overlaying a table with a rept measure to convert numbers to icons, the problem with this is that I had to create 3 cards for every intersection between Team & Stage (one for each value category), on top of each other. The problem with this is that it doesn't exactly match the image above as the different value categories don't lay next to each other but on top and also there is now over 100 independent cards making up the graph, which is resource intensive.

Some of the problems I am encountering are:

  • Icons don't wrap, they continue to move horizontally so after a certain number of icons an ellipsis is seen after the icons.

  • Conditional formatting with color measures don't work, as Power BI is preferring to create a conditional column in place of a measure, this just colors all icons with the first color. This is the reason I have not used multi-row cards.

The data I have looks something like this:

 

ProjectTeamTypeStage
Project 1Team 1Type 1Stage 1
Project 2Team 1Type 2Stage 2
Project 3Team 1Type 3Stage 3
Project 4Team 2Type 1Stage 4
Project 5Team 2Type 2Stage 1
Project 6Team 2Type 3Stage 2
Project 7Team 3Type 1Stage 3
Project 8Team 3Type 2Stage 4
Project 9Team 3Type 3Stage 1
Project 10Team 4Type 1Stage 2
Project 11Team 4Type 2Stage 3
Project 12Team 4Type 3Stage 4
Project 13Team 1Type 3Stage 2
Project 14Team 2Type 2Stage 1
Project 15Team 2Type 2Stage 1
Project 16Team 3Type 2Stage 1
Project 17Team 4Type 2Stage 1

 

I am relatively new to Power BI so I'm not sure if there is a more obvious way for me to get to my desired graph that I have overlooked. The idea would be to automate the process so mapping it onto a scatter plot or some other type of plot with numbers is likely out of the question.

 

My questions would be:

  • Are there anything alternative solutions I have not considered, or any obvious mistakes I have made?

  • Are there any work arounds for the problems I mentioned above regarding color conditioning and measures, and icons wrapping?

Any help would be greatly appreciated, and apologies if I have asked this question incorrectly or missed any useful information.

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @stevenmcginnis 

Please try function 'Unichar' and 'Rept' in Dax.

Sample:

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" )
    )

veasonfmsft_0-1668156625597.png

https://unicode-table.com/en/1F7E6/ 

https://unicode-table.com/en/1F7E8/ 

https://unicode-table.com/en/1F7E5/ 

 

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi, @stevenmcginnis 

Please try function 'Unichar' and 'Rept' in Dax.

Sample:

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" )
    )

veasonfmsft_0-1668156625597.png

https://unicode-table.com/en/1F7E6/ 

https://unicode-table.com/en/1F7E8/ 

https://unicode-table.com/en/1F7E5/ 

 

Best Regards,
Community Support Team _ Eason

That's amazing, thank you.

 

I had been doing something similar with the rept function and unichar but didn't realise they could be passed through as values in the matrix table. 

 

Just some follow up questions if you don't mind:

  • Is it possible to wrap the squares? I imagine when projects within an intersection reach a high enough number it might present problems.
  • Is it possible to set all columns to be the same width?
  • Some of the totals in my graph have turned into squares, what is likely to have gone wrong? It appears to happen when a row or column only has one value. Is there a way around this?
  • In your square measure what does "General Number" do?

Again thank you, you have no idea how long I've been trying to figure this out.

Hi. @stevenmcginnis 

1.I think the text wraps automatically when you resize the column width.

veasonfmsft_0-1669974906126.png

2. Maybe you can increase the height from Visualizations>>FORMAT Pane>>Grid>>Row padding

veasonfmsft_1-1669975418043.png

 

3. I am sorry for my oversight. Here we need to use ISINSCOPE rather than HASONEVALUE in formula. 
Reference:

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/

square1 = 
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 _square =
    REPT ( UNICHAR ( "128998" ), _type1 ) & REPT ( UNICHAR ( "128997" ), _type2 )
        & REPT ( UNICHAR ( "129000" ), _type3 )
RETURN
    IF (
        ISINSCOPE( 'Table'[Stage] )&&ISINSCOPE('Table'[Team]),
            _square,
            FORMAT ( COUNT ( 'Table'[Type] ), "General Number" )
    )

 

4. Just used to format the string, make sure the total value is displayed with no formatting

https://learn.microsoft.com/en-us/dax/format-function-dax#format-strings 

 

Best Regards,
Community Support Team _ Eason

That's amazing, thank you again!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.