March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
to create a measure/calculated column that can be used in a Stacked Column Chart where:
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!
Solved! Go to 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
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
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!
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
87 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |