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

[Anonymous]

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.

[MFelix]

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]

 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]

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

[Anonymous]

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

)

)

Can you see what mistake am I doing here
DC-R TV is my table, updatedmembernumber is my storeID, Dollars is my Sales numbers.

[MFelix · Accepted solution]

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

[Anonymous]

@MFelix  How do we add, the total sales and the average sales number to this matrix.

Thanks.

[MFelix]

Hi @Anonymous ,

You need to create the new measures as I refered on previous response and then place it on the values of the matrix setup.

Regards,

MFelix

[Anonymous]

This is great ! Thanks a Ton.  I was able to add percentage of door count as well. 

How do we add, the total sales number to this. I mean in that range what is the exact number of sales did it make?
and the average sale that each store made between the range.

[Anonymous]

@MFelix 

Could you please help me with the below.

How can we add, the total sales number to this. I mean in that range what is the exact number of sales did it make?
and the average sale that each store made between the range.

[MFelix]

Hi @Anonymous 

Try the following code:

Sales_Total =
IF (
    HASONEFILTER ( BinSelect[Description] ),
    SUMX (
        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] )
            )
        ),
        [Total Sales$]
    ),
    SUM ( Sales[Sales$] )
)

Tried to adjust the formula to the names of your table based on the previous measure you send out.

Regards,

MFelix