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

     

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

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's avatar
gmsamborn
Icon for Super User rankSuper User
1 year ago

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.