Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to Group a measure or a numeric value (Sales$) or Set up multiple numeric ranges

I have Store Id's, month and sales$ for 2019. This is my raw data. I want to Know how many of the storeId's fall in the ranges i mentioned below in the second picture.

 

I want the result like below. (This is number of stores that fall under the range given, after summing up the sales of all the months of 2019)


How do I do this. Thank you for your response.

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Believe that the issue is related with a semi-comma that you didn't replace by a comma. Try the following:

     

    DoorCountUnique =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALL ( 'DC-R TV'[UpdateMemberNum] ),
            'DC-R TV'[UpdateMemberNum],
            "Total Sales$", SUM ( 'DC-R TV'[Dollars] )
        ),
        [Total Sales$] <= MAX ( BinSelect[MaxValueRange] )
            && [Total Sales$] >= MAX ( BinSelect[MinValueRange] )
    )
)

    Regards,

    MFelix

10 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    Create an unrelated table with the following format:

     

    Sales_Range

    ID Description Min Max
    1 0-30 0 30
    2 30-100 31 100
    3 100-200 101 200
    4 200-400 201 400
    5 >400 401 9999999999

     

    (I created the higher than 400 because your values don't sum as you have in the split, but this can be change to whatever values you want.

     

    Now create the following measure:

    Store_Count_Unique =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALL ( Sales[Store_ID] );
            Sales[Store_ID];
            "Total Sales"; SUM ( Sales[Sales] )
        );
        [Total Sales] <= MAX ( Sales_Range[Max] )
            && [Total Sales] >= MAX ( Sales_Range[Min] )
    )
)

    Result is in the image below and the PBIX file attach.

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
       ALL ( Sales[Store_ID] );
            Sales[Store_ID];
            "Total Sales"; SUM ( Sales[Sales] )

      COuld you please explain the above part in the query. 

      Is the Sales[Store_ID] = Table[column].

      If so what does "Total Sales" in the line represent ? 

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi Anonymous ,

         

        What I'm doing in the measure I created is a temporary table that makes the sum per store of the sales. In that temporary table I need to name the SUM of the sales and I decided to call it Total Sales.

         

        This virtual table column is then used on the filter part to check if the store should be counted on your ranges or not.

         

        You can call it whatever you want as long as the name is then reused on the filter part of the measure.

         

        Regards,

        MFelix