Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Gurus!
I have a list of stores and plates that I want to chart out. The issue is I am trying to figure out how to plot (on a line graph) the store that has the most amount of sales over the time period. I tried Calculate/MAX however it looks like it is only plotting the highest value per day.
The goal is to have (depending on the slicer/plate selected) have the chart show the store that sold the most during the whole time period.
An example result would be for plate "a", i would expect to have store #5 plotted. For plate "b" it would be store #3.
Here is the datatable i am working with:
Store # | Plate | Date | Quantity Sold |
1 | a | 7/1/2018 | 44 |
1 | b | 7/1/2018 | 37 |
1 | c | 7/1/2018 | 50 |
1 | a | 7/2/2018 | 38 |
1 | b | 7/2/2018 | 43 |
1 | c | 7/2/2018 | 57 |
1 | a | 7/3/2018 | 35 |
1 | b | 7/3/2018 | 67 |
1 | c | 7/3/2018 | 88 |
1 | a | 7/4/2018 | 80 |
1 | b | 7/4/2018 | 59 |
1 | c | 7/4/2018 | 27 |
1 | a | 7/5/2018 | 70 |
1 | b | 7/5/2018 | 52 |
1 | c | 7/5/2018 | 25 |
1 | a | 7/6/2018 | 76 |
1 | b | 7/6/2018 | 67 |
1 | c | 7/6/2018 | 36 |
1 | a | 7/7/2018 | 85 |
1 | b | 7/7/2018 | 35 |
1 | c | 7/7/2018 | 31 |
1 | a | 7/8/2018 | 41 |
1 | b | 7/8/2018 | 43 |
1 | c | 7/8/2018 | 40 |
1 | a | 7/9/2018 | 41 |
1 | b | 7/9/2018 | 46 |
1 | c | 7/9/2018 | 68 |
2 | a | 7/1/2018 | 37 |
2 | b | 7/1/2018 | 23 |
2 | c | 7/1/2018 | 16 |
2 | a | 7/2/2018 | 81 |
2 | b | 7/2/2018 | 92 |
2 | c | 7/2/2018 | 77 |
2 | a | 7/3/2018 | 72 |
2 | b | 7/3/2018 | 25 |
2 | c | 7/3/2018 | 44 |
2 | a | 7/4/2018 | 12 |
2 | b | 7/4/2018 | 22 |
2 | c | 7/4/2018 | 42 |
2 | a | 7/5/2018 | 91 |
2 | b | 7/5/2018 | 63 |
2 | c | 7/5/2018 | 93 |
2 | a | 7/6/2018 | 16 |
2 | b | 7/6/2018 | 82 |
2 | c | 7/6/2018 | 43 |
2 | a | 7/7/2018 | 21 |
2 | b | 7/7/2018 | 76 |
2 | c | 7/7/2018 | 67 |
2 | a | 7/8/2018 | 86 |
2 | b | 7/8/2018 | 36 |
2 | c | 7/8/2018 | 37 |
2 | a | 7/9/2018 | 98 |
2 | b | 7/9/2018 | 90 |
2 | c | 7/9/2018 | 75 |
3 | a | 7/1/2018 | 49 |
3 | b | 7/1/2018 | 66 |
3 | c | 7/1/2018 | 17 |
3 | a | 7/2/2018 | 92 |
3 | b | 7/2/2018 | 33 |
3 | c | 7/2/2018 | 71 |
3 | a | 7/3/2018 | 40 |
3 | b | 7/3/2018 | 84 |
3 | c | 7/3/2018 | 15 |
3 | a | 7/4/2018 | 82 |
3 | b | 7/4/2018 | 74 |
3 | c | 7/4/2018 | 22 |
3 | a | 7/5/2018 | 25 |
3 | b | 7/5/2018 | 92 |
3 | c | 7/5/2018 | 82 |
3 | a | 7/6/2018 | 34 |
3 | b | 7/6/2018 | 25 |
3 | c | 7/6/2018 | 57 |
3 | a | 7/7/2018 | 45 |
3 | b | 7/7/2018 | 45 |
3 | c | 7/7/2018 | 20 |
3 | a | 7/8/2018 | 90 |
3 | b | 7/8/2018 | 60 |
3 | c | 7/8/2018 | 74 |
3 | a | 7/9/2018 | 30 |
3 | b | 7/9/2018 | 96 |
3 | c | 7/9/2018 | 16 |
4 | a | 7/1/2018 | 35 |
4 | b | 7/1/2018 | 79 |
4 | c | 7/1/2018 | 95 |
4 | a | 7/2/2018 | 56 |
4 | b | 7/2/2018 | 70 |
4 | c | 7/2/2018 | 60 |
4 | a | 7/3/2018 | 14 |
4 | b | 7/3/2018 | 19 |
4 | c | 7/3/2018 | 77 |
4 | a | 7/4/2018 | 49 |
4 | b | 7/4/2018 | 74 |
4 | c | 7/4/2018 | 77 |
4 | a | 7/5/2018 | 82 |
4 | b | 7/5/2018 | 77 |
4 | c | 7/5/2018 | 15 |
4 | a | 7/6/2018 | 48 |
4 | b | 7/6/2018 | 43 |
4 | c | 7/6/2018 | 57 |
4 | a | 7/7/2018 | 47 |
4 | b | 7/7/2018 | 16 |
4 | c | 7/7/2018 | 42 |
4 | a | 7/8/2018 | 16 |
4 | b | 7/8/2018 | 73 |
4 | c | 7/8/2018 | 52 |
4 | a | 7/9/2018 | 98 |
4 | b | 7/9/2018 | 47 |
4 | c | 7/9/2018 | 64 |
5 | a | 7/1/2018 | 51 |
5 | b | 7/1/2018 | 37 |
5 | c | 7/1/2018 | 57 |
5 | a | 7/2/2018 | 88 |
5 | b | 7/2/2018 | 86 |
5 | c | 7/2/2018 | 91 |
5 | a | 7/3/2018 | 67 |
5 | b | 7/3/2018 | 58 |
5 | c | 7/3/2018 | 46 |
5 | a | 7/4/2018 | 75 |
5 | b | 7/4/2018 | 21 |
5 | c | 7/4/2018 | 27 |
5 | a | 7/5/2018 | 51 |
5 | b | 7/5/2018 | 87 |
5 | c | 7/5/2018 | 12 |
5 | a | 7/6/2018 | 62 |
5 | b | 7/6/2018 | 72 |
5 | c | 7/6/2018 | 33 |
5 | a | 7/7/2018 | 99 |
5 | b | 7/7/2018 | 84 |
5 | c | 7/7/2018 | 40 |
5 | a | 7/8/2018 | 74 |
5 | b | 7/8/2018 | 15 |
5 | c | 7/8/2018 | 75 |
5 | a | 7/9/2018 | 60 |
5 | b | 7/9/2018 | 51 |
5 | c | 7/9/2018 | 12 |
Solved! Go to Solution.
Hi @danb,
1. Create the two measures:
the_store_for_max_sales = VAR maxstore = CALCULATE ( MAX ( 'Table'[Store #] ), FILTER ( 'Table', 'Table'[Quantity Sold] = MAX ( 'Table'[Quantity Sold] ) ) ) RETURN CALCULATE ( SUM ( 'Table'[Quantity Sold] ), FILTER ( 'Table', 'Table'[Store #] = maxstore ) )
Measure 2 = VAR temp = MAXX ( TOPN ( 1, SUMMARIZE ( ALLSELECTED ( 'Table' ), [Plate], [Store #], "Total", SUM ( [Quantity Sold] ) ), [Total], DESC ), [Store #] ) RETURN IF ( MAX ( 'Table'[Store #] ) = temp, "Y", "N" )
2. Drag the measure2 to visual level filter like below.
For more details, you could have a reference of the attachment.
Best Regards,
Cherry
Hi @danb,
If I understand your requirement correctly that you want to get the output like below?
If it is, you could refer to the measure below.
the_store_for_max_sales = CALCULATE ( MAX ( 'Table'[Store #] ), FILTER ( 'Table', 'Table'[Quantity Sold] = MAX ( 'Table'[Quantity Sold] ) ) )
Then you could create the line chart with the Date and the measure.
Best Regards,
Cherry
Cherry,
I am actually trying to plot the actual sales of whatever store sold the most for that selected plate. For example if I was looking at plate "A" i would want the max chart to plot the daily sales from only Store #5 because it sold the most of all of the stores from that time period.
Sorry for the un-clarity on the original post.
Thank you for you help in this!
Dan
Hi @danb,
1. Create the two measures:
the_store_for_max_sales = VAR maxstore = CALCULATE ( MAX ( 'Table'[Store #] ), FILTER ( 'Table', 'Table'[Quantity Sold] = MAX ( 'Table'[Quantity Sold] ) ) ) RETURN CALCULATE ( SUM ( 'Table'[Quantity Sold] ), FILTER ( 'Table', 'Table'[Store #] = maxstore ) )
Measure 2 = VAR temp = MAXX ( TOPN ( 1, SUMMARIZE ( ALLSELECTED ( 'Table' ), [Plate], [Store #], "Total", SUM ( [Quantity Sold] ) ), [Total], DESC ), [Store #] ) RETURN IF ( MAX ( 'Table'[Store #] ) = temp, "Y", "N" )
2. Drag the measure2 to visual level filter like below.
For more details, you could have a reference of the attachment.
Best Regards,
Cherry
@v-piga-msft - one last question, I am trying to flip it now and identify the Minimum sales store. Was thinking it was as simple as changing the MAXs to MINs and flipping the "DESC" to "ASC" in Measure2 however that is not working. Any recommendations?
Dan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |