cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trdoan Member
Member

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

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: DAX in a Chart

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.
3 REPLIES 3
v-yulgu-msft Super Contributor
Super Contributor

Re: DAX in a Chart

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.
trdoan Member
Member

Re: DAX in a Chart

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!

Highlighted
v-yulgu-msft Super Contributor
Super Contributor

Re: DAX in a Chart

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 35 members 1,024 guests
Please welcome our newest community members: