Show selected and remaining in Power BI pie chart

1
votes

I need to create a pie or donut chart in power bi that displays the percentage of the selected items from a slicer in relation to the grand total in the same visual while keeping the legends.

Ex: 

---------------------------
| Id | Product | Category |
| 1  |   P1    |    A     |
| 2  |   P2    |    A     |
| 3  |   P3    |    A     |
| 4  |   P4    |    B     |
| 5  |   P5    |    B     |
| 6  |   P6    |    B     |
| 7  |   P7    |    C     |
| 8  |   P8    |    C     |
| 9  |   P9    |    D     |
---------------------------

I have a slicer with Category A,B,C,D IF I select A, B; I want the pie chart to show A, B then combines the remaining not selected values as the rest of the Pie chart so it shows three categories A, B, remainder "C&D" together.

I have the following Measures to count the selected and not selected values.

But when using two measures in a pie or donut chart it displays them as subcategories.

Count not Selected Products =
CALCULATE (
    COUNT ( Products[Product] ),
    FILTER (
        ALL ( Products[Category] ),
        NOT Products[Category] IN VALUES ( Products[Category] )
    )
)

Count Selected =
CALCULATE (
    COUNT ( Products[ID] ),
    FILTER ( Products, Products[Category] IN ALLSELECTED ( Products[Category] ) )
)

Is it possible to do it in power bi ?

enter image description here

1
powerbidax

1 Answers

0
votes

First, you'll need to define a new table including "Remaining"to use in the pie chart.

Something like this should work:

Pie = UNION ( VALUES ( Products[Category] ), { { "Remaining" } } )

This is just a single column table:

Category
--------
A
B
C
D
Remaining

Put this newly defined column as the Legend for the pie chart and write a measure as follows:

CountProducts =
IF (
    SELECTEDVALUE ( Pie[Category] ) = "Remaining",
    CALCULATE (
        COUNT ( Products[Product] ),
        ALL ( Products[Category] ),
        NOT ( Products[Category] IN VALUES ( Products[Category] ) )
    ),
    CALCULATE (
        COUNT ( Products[Product] ),
        FILTER ( Products, Products[Category] IN VALUES ( Pie[Category] ) )
    )
)

The first part is exactly what you had and the second part is very similar.

Pie Chart