Forum Discussion

polman4's avatar
polman4
Helper I
3 years ago
Solved

Distinctcount values by custom filter

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$, - bet...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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's avatar
    rsbin
    3 years ago

    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's avatar
polman4
Helper I
3 years ago

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's avatar
rsbin
Community Champion
3 years ago

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's avatar
    polman4
    Helper I
    3 years ago

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