DAX in a Chart

[trdoan]

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!

[v-yulgu-msft · Accepted solution]

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]

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:

Store	Year	W Sales	Global Sales	W Sales Percentage
A	2015	3,000,000	6,500,000	46.15%
A	2016	7,800,000	15,000,000	52.00%
A	2017	560,000	7,500,000	7.47%
A	2018	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]

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