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! 

      • danb's avatar
        danb
        Resolver I
        8 years ago

        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