Distinctcount values by custom filter

[polman4]

Hello,

I have a table (it's created with append) where I want to count how many products sold between specific total revenues by year. For example some random numbers:
- less than 10000$,

- between 10001$ and 19999$

- more than 20000$

My dataset has more than 7m rows so I created a sample one.

So here is a simple sum of each product per year

and here is what i achieve and i what i need

Here is a sample file: https://we.tl/t-LPRNcQkPzl
The revenue margins are custom by me, 
I don't know what the best way to create the custom revenue margins, i tried to create another table with just one column and dragging this column which it works but i don't know how to "filter" distinct count with this value.

Any help would be appreciated! Thank you

[v-zhangti · Accepted solution]

Hi, @polman4 

You can try the following methods.

Calculated Column:

Sum =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        [Product] = EARLIER ( 'Table'[Product] )
            && [Year] = EARLIER ( 'Table'[Year] )
    )
)

Revenue = SWITCH(TRUE(),
[Sum]>=0&&[Sum]<=10,"0 to 10",
[Sum]>10&&[Sum]<=20,"11 to 20",
[Sum]>20&&[Sum]<=30,"21 to 30")

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[polman4]

Hello both,

Thank you so much for the time spent, really really helpful info and I learned a lot also.
A note: My Append table instead of a column named "Year" has a Date/time field.
Let me make some comments:

@rsbin 
Your solution worked perfect for me.

I just tried an extra step but i wasn't successful. I tried to add a filter to summarizecolumn

Sites_per_year = 
SUMMARIZECOLUMNS(
        Append1[Time].[Year], 
        Append1[Name (Site)],
        FILTER (Append1, Append1[Time].[Year] = 2022),
        "Inventory", sum(Append1[Total inventory])
    )

Now the column year doesn't excist anymore and it's turned to "Time" with date hierarchy. And it's not really working:

Also i dont have all the categorizations for a reason. If I use 

@v-zhangti 
Your example is what i wanted but the problem is i can't replicate it on the original data.. I think the caclulations I do on the new calculated column are wrong:

b.sum_per_year = 
CALCULATE(
    SUM(Append1[Total inventory]),
    FILTER(
        'Append1',
        [Name (Site)] = EARLIER('Append1'[Name (Site)])
            && Append1[Time].[Year] = EARLIER(Append1[Time].[Year])
    )
)

and here is the result:

I think on both occasions my problem is that i don't know how to handle correctly a column with date/time structure..

Any addition help would be usefull 🙂

Thank you,

Greg

[rsbin · Accepted solution]

@polman4 ,

Since I don't have your new file, I will make a couple of suggestions:

1) Create a new Calculated Column:  Year = YEAR( Append1[Time] ).  This will make things easier to see as you continue to progress.

I believe you have the Filter in the wrong spot in your new Sites_per_year table.

Sites_per_year = 
SUMMARIZECOLUMNS(
        Append1[Year], 
        Append1[Name (Site)],
        "Inventory", sum(Append1[Total inventory]),
        FILTER (Append1, Append1[Year] = 2022))

Give this a shot and hopefully you can continue on your way.

Regards,

[polman4]

Thank you @rsbin 

This worked fine!

For anyone reading the thread this is final code i used to create the summarize table:

Sites_per_year = 
SUMMARIZECOLUMNS(
        Append1[c.Year],
        Append1[Name (Site)],
        Filter(Append1, Append1[c.Month] = 01 
                        || Append1[c.Month] = 02 
                        || Append1[c.Month] = 03
                        || Append1[c.Month] = 04
                        || Append1[c.Month] = 05
                        || Append1[c.Month] = 06
                        || Append1[c.Month] = 07
                        || Append1[c.Month] = 08
                        || Append1[c.Month] = 09),
        "Inventory", sum(Append1[Total inventory])
    )

[v-zhangti · Accepted solution]

Hi, @polman4 

You can try the following methods.

Calculated Column:

Sum =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        [Product] = EARLIER ( 'Table'[Product] )
            && [Year] = EARLIER ( 'Table'[Year] )
    )
)

Revenue = SWITCH(TRUE(),
[Sum]>=0&&[Sum]<=10,"0 to 10",
[Sum]>10&&[Sum]<=20,"11 to 20",
[Sum]>20&&[Sum]<=30,"21 to 30")

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rsbin]

@polman4 ,

Please see revised pbix file attached.

First created a Summary Table that creates your annual totals.

Then a Calculated Column using SWITCH to determine which "Revenue Bin" each product applies to.

Trust this is what you are after.

Regards,