Forum Discussion

trdoan's avatar
trdoan
Icon for Helper III rankHelper III
7 years ago
Solved

DAX in a Chart

Hello everyone,

 

My sample data can be found here

 

Assuming I have a "Store Name" slicer made from 'General Info'[Store Name] and a random store name is selected, I'd like to use the following 3 tables:

  • General Info (has all store names)
  • Global Sales
  • W Sales

to create a measure/calculated column that can be used in a Stacked Column Chart where:

  • X-Axis: Year
  • Value: The selected Store's corresponding Global Sales & W Sales
  • Legend: "Global Sales" ; "W Sales"
  • The chart displays only 1 Store's sales data and if no Store is selected from the "Store Name" slicer, leave the chart blank

Is there a way to concatenate Global Sales & W Sales into the same graph all the while showing only 1 Store at a time? It looks like an easy task, however, I can't seem to find a way to run both Global Sales & W Sales under the same graph. My graph would always have multiple Stores instead of 1 if I didn't make any selection in the "Store Name" slicer.

 

Please advise! Thank you very much!

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi trdoan ,

     

    An extra year dimention table is needed, which is linked to both 'Global Sales' and 'W Sales' tables based on common field [Year].

    Year = VALUES('Global Sales'[Year])

     

    Add [Store Name] from the 'General Info' into slicer. Place [Year] from above calculated table 'Year' onto X-axis, and add below measures into "values" of chart.

    Global sales measure =
IF (
    ISFILTERED ( 'General Info'[Store Name] ),
    SUM ( 'Global Sales'[Global Sales] ),
    BLANK ()
)

W sales measure =
IF (
    ISFILTERED ( 'General Info'[Store Name] ),
    SUM ( 'W Sales'[W Sales] ),
    BLANK ()
)

    Best regards,

    Yuliana Gu

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi trdoan ,

     

    An extra year dimention table is needed, which is linked to both 'Global Sales' and 'W Sales' tables based on common field [Year].

    Year = VALUES('Global Sales'[Year])

     

    Add [Store Name] from the 'General Info' into slicer. Place [Year] from above calculated table 'Year' onto X-axis, and add below measures into "values" of chart.

    Global sales measure =
IF (
    ISFILTERED ( 'General Info'[Store Name] ),
    SUM ( 'Global Sales'[Global Sales] ),
    BLANK ()
)

W sales measure =
IF (
    ISFILTERED ( 'General Info'[Store Name] ),
    SUM ( 'W Sales'[W Sales] ),
    BLANK ()
)

    Best regards,

    Yuliana Gu

    • trdoan's avatar
      trdoan
      Icon for Helper III rankHelper III
      7 years ago

      Hi v-yulgu-msft , thank you for your help! Everything worked perfectly! 

       

      However, I forgot to ask in the first place but please may you help me to compute the W Sales Percentages compared to Global Sales for each year?

       

      Ex: Take Store A for example:

       

      StoreYearW SalesGlobal SalesW Sales Percentage
      A2015  3,000,000       6,500,000 46.15%
      A2016  7,800,000    15,000,000 52.00%
      A2017     560,000       7,500,000 7.47%
      A2018     250,000       1,500,000 16.67%

       

      How would you create a measure to calculate each year's W sales percentage of each selected Store and if either W Sales or Global Sales is not available, return "N/A" ? 

       

      Thank you so much!

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi trdoan ,

         

        Change "Stakced Column cahrt" to "Line and Stacked column chart", add the percentage calculation to "Line values".

        W Sales Percentage =
IF (
    [W sales measure] = BLANK ()
        || [Global sales measure] = BLANK (),
    "N/A",
    [W sales measure] / [Global sales measure]
)

         

        Best regards,

        Yuliana Gu