Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
98 | |
96 | |
38 | |
36 |
User | Count |
---|---|
151 | |
125 | |
75 | |
74 | |
53 |