Creating a fake bar chart category

[EugenioProlog]

I have this report that uses my sales_by_salesperson table to plot a bar chart. I also have a table called sales_online with several categories and I want to sum them all into one, as if they were a salesperson called "online" and be included in the legend of the bar chart. How can I do that?

Here is the link to my dashboard.

https://drive.google.com/file/d/1Ay4LF2i0qC0GmXmrqJpOxa39hc9uhwb1/view?usp=drive_link

[gmsamborn]

Hi @EugenioProlog 

After rethinking this, I came up with a solution that doesn't have to duplicate the sales tables.  (I'm not sure what I was thinking.)

I created the following dimension table to be related to both fact tables.

Salesperson = 
    UNION(
        DISTINCT( sales_by_salesperson[salesperson] ),
        { "Online" }
    )

The measure would be like this.

_Sales = 
    IF(
        SELECTEDVALUE( 'Include Online'[Include Online?] ) = "Yes",
        SUMX(
            'sales_by_salesperson',
            [total]
        ) +
            SUMX(
                'sales_online',
                [total]
            ),
        SUMX(
            'sales_by_salesperson',
            [total]
        )
    )

Sales Bar Chart - 3.pbix

This makes a lot more sense.

[EugenioProlog]

Is there a way to create a data slicer that only displays the option "Online" (on/off)? I want my other categories to be always visible, the user can only choose to display or hide the Online category on the data slicer.

[gmsamborn]

Hi @EugenioProlog 

I created a 'Include Online" table as follows and sorted it.  (There is Power Query step to rename the [Response] column to [Include Online?].)

Add a slicer using the new table and then I replaced my orignal measure with [_Sales]:

_Sales = 
    IF(
        SELECTEDVALUE( 'Include Online'[Include Online?] ) = "Yes",
        SUMX(
            'Calculated Table',
            [_Total]
        ),
        SUMX(
            FILTER(
                'Calculated Table',
                'Calculated Table'[_Salesperson] <> "Online"
            ),
            [_Total]
        )
    )

Sales Bar Chart - 2.pbix

I hope this helps.

[gmsamborn · Accepted solution]

Hi @EugenioProlog 

To accomplish this, I created a calculated table.

Calculated Table = 
    UNION(
        SELECTCOLUMNS(
            'sales_by_salesperson',
            "_Date", [date],
            "_Salesperson", [salesperson],
            "_Total", [total]
        ),
        SELECTCOLUMNS(
            'sales_online',
            "_Date", [date],
            "_Salesperson", "Online",
            "_Total", [total]
        )
    )

After relating the new table to dim_calendar based on [Date], I created the following measure:

Sales including Online = 
    SUMX(
        'Calculated Table',
        [_Total]
    )

Let me know if you have any questions.

Sales Bar Chart.pbix