Forum Discussion

danb's avatar
danb
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 t...
Need Help
  • v-piga-msft's avatar
    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

v-piga-msft's avatar
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's avatar
    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's avatar
      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

      • danb's avatar
        danb
        Resolver I
        8 years ago

        v-piga-msft

        That works. Thank you so much!