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 #PlateDateQuantity Sold1a7/1/2018441b7/1/2018371c7/1/2018501a7/2/2018381b7/2/2018431c7/2/2018571a7/3/2018351b7/3/2018671c7/3/2018881a7/4/2018801b7/4/2018591c7/4/2018271a7/5/2018701b7/5/2018521c7/5/2018251a7/6/2018761b7/6/2018671c7/6/2018361a7/7/2018851b7/7/2018351c7/7/2018311a7/8/2018411b7/8/2018431c7/8/2018401a7/9/2018411b7/9/2018461c7/9/2018682a7/1/2018372b7/1/2018232c7/1/2018162a7/2/2018812b7/2/2018922c7/2/2018772a7/3/2018722b7/3/2018252c7/3/2018442a7/4/2018122b7/4/2018222c7/4/2018422a7/5/2018912b7/5/2018632c7/5/2018932a7/6/2018162b7/6/2018822c7/6/2018432a7/7/2018212b7/7/2018762c7/7/2018672a7/8/2018862b7/8/2018362c7/8/2018372a7/9/2018982b7/9/2018902c7/9/2018753a7/1/2018493b7/1/2018663c7/1/2018173a7/2/2018923b7/2/2018333c7/2/2018713a7/3/2018403b7/3/2018843c7/3/2018153a7/4/2018823b7/4/2018743c7/4/2018223a7/5/2018253b7/5/2018923c7/5/2018823a7/6/2018343b7/6/2018253c7/6/2018573a7/7/2018453b7/7/2018453c7/7/2018203a7/8/2018903b7/8/2018603c7/8/2018743a7/9/2018303b7/9/2018963c7/9/2018164a7/1/2018354b7/1/2018794c7/1/2018954a7/2/2018564b7/2/2018704c7/2/2018604a7/3/2018144b7/3/2018194c7/3/2018774a7/4/2018494b7/4/2018744c7/4/2018774a7/5/2018824b7/5/2018774c7/5/2018154a7/6/2018484b7/6/2018434c7/6/2018574a7/7/2018474b7/7/2018164c7/7/2018424a7/8/2018164b7/8/2018734c7/8/2018524a7/9/2018984b7/9/2018474c7/9/2018645a7/1/2018515b7/1/2018375c7/1/2018575a7/2/2018885b7/2/2018865c7/2/2018915a7/3/2018675b7/3/2018585c7/3/2018465a7/4/2018755b7/4/2018215c7/4/2018275a7/5/2018515b7/5/2018875c7/5/2018125a7/6/2018625b7/6/2018725c7/6/2018335a7/7/2018995b7/7/2018845c7/7/2018405a7/8/2018745b7/8/2018155c7/8/2018755a7/9/2018605b7/9/2018515c7/9/201812

Resolver I

8 years ago

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 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

Need Help

v-piga-msft

8 years ago

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

top store.pbix42 KB

5 Replies

v-piga-msft
Resident Rockstar
8 years ago
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
  Resolver I
  8 years ago
  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
    Resident Rockstar
    8 years ago
    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
    top store.pbix42 KB

Forum Discussion

Plot Store with Max Sales over Time Period

5 Replies

Related content

Sales LY till same period

Measure for Same Store Sales Growth

Sales Last month and Last month same period

Sales total For Specific period

Showing 13 periods