Forum Discussion

Chris203's avatar
Chris203
New Member
3 years ago

Counting unique values in a multi value cell

Hi there,

 

I built a small status report using MS List and am by no means a programmer or math-wiz. This is just to help our small team better report on work done.

I need urgent assistance with the reporting in PowerBI desktop.

I get my data from MS List. A part of the data is multiple selections in one cell.

i.e.

 

Selected items

Sale 10

B; C; D; E; A

Sale 12

A; C; D

Sale 13

D; A; B; E

Sale 14

A;

Sale 15

C; D; A; E

Sale 16

A; C; E

Sale 17

D ; A; C;

 

I need a clustered column-chart in PowerBI that shows number of sales per item

A=7; B=2; C=5; D=5; E=4

 

Currently I can only get PowerBI to show the following.

 

Obviously, there is more to my list and these couple of these fields that needs the same treatment.

Note: there is also amounts in the rows and by using “Unpivot” these get duplicated, and my totals then stop being true.

Need Help

1 Reply

  • Washivale's avatar
    Washivale
    Resolver V
    3 years ago

    Hey Chris203 ,
    you might need a list of unique items that goes to the concatenated column to get what you are looking for

    probably something like below
    List Items
    A
    B

    C
    D

    then you will write a measure 

    Counts = var __list_item = MAX(List_Items[Values])
    return
    CALCULATE(COUNT('Table'[Selected Items]), CONTAINSSTRING('Table'[Selected Items],__list_item))

    then you will use List_Items[Values] and newly created measure on your clustered chart


     


    thanks,
    Sandeep