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.