Plot Store with Max Sales over Time Period

[danb]

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

[v-piga-msft]

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

[danb]

@v-piga-msft

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

[v-piga-msft · Accepted 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

[danb]

@v-piga-msft

That works. Thank you so much! 

[danb]

@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