I have multiple columns of the same type of data in a Power BI dataset

[colinryan]

Hello, 

I have 3 columns for genre data in a Power BI dataset and I want to create a chart that shows those columns as a single data point,

I want to count all three as a single data point in charts like this bar graph

how should I go about it?

Thank you!

[v-yilong-msft]

Hi @colinryan ,

I create a table as you mentioned.

Then I create a new table and here is the DAX code.

Table 2 = 
SUMMARIZE(
    FILTER(
        UNION(
            SELECTCOLUMNS('Table', "Number", 'Table'[Genre1]),
            SELECTCOLUMNS('Table', "Number", 'Table'[Genre2]),
            SELECTCOLUMNS('Table', "Number", 'Table'[Genre3])
        ),
        [Number] <> BLANK()
    ),
    [Number],
    "Frequency", COUNTROWS(FILTER(
        UNION(
            SELECTCOLUMNS('Table', "Number", 'Table'[Genre1]),
            SELECTCOLUMNS('Table', "Number", 'Table'[Genre2]),
            SELECTCOLUMNS('Table', "Number", 'Table'[Genre3])
        ),
        [Number] = EARLIER([Number]) && [Number] <> BLANK()
    ))
)

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @colinryan ,

You need to transform your data so you only have one column for the three genre columns. In Power Query, select the title column, right click and then select unpivot other columns which will create two new columns - attribute and value. Rename them as desired. Create a measure to count the distinct titles ( use DISTINCTCOUNT function). Use the value column and newly created measure in your visual.

[colinryan]

Sorry I forgot to include, I also have a column for their respective gross profits. Won't unpivoting columns affect the totals of the gross profit column upon visualizing?