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 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

 

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

     

4 Replies

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    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.

       

  • 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.