Forum Discussion
Solved
Plot Store with Max Sales over Time Period
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 t...
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,
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
That works. Thank you so much!
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