Forum Discussion

EugenioProlog's avatar
EugenioProlog
Icon for Helper III rankHelper III
1 year ago
Solved

Creating a fake bar chart category

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 ...
Need Help
  • gmsamborn's avatar
    gmsamborn
    1 year ago

    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

     

gmsamborn's avatar
gmsamborn
Icon for Super User rankSuper User
1 year ago

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.