Forum Discussion

trdoan's avatar
trdoan
Helper 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 ...
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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft 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
    Helper 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
      Microsoft 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