Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trdoan
Helper III
Helper III

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!

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

3.PNG

 

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

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

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

3.PNG

 

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

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.