Problem creating an aggregation based on a measure

[FabioF]

Hi everyone, I'm struggling with the issue in the subject of this message.
I'll try to explain the scenario as best as I can, I apologize in advantage for my bad english.

I have a table (let's name it TBL) that looks like this: (N.B. Date format is DD-MM-YYYY)

ID	CATEGORY	DATE
1	A	01/01/2023
2	A	01/02/2022
3	B	18/04/2024
4	C	06/05/2024
5	C	14/06/2024
6	C	19/07/2024
7	A	20/08/2024
8	B	21/08/2024
9	B	22/08/2024
10	B	23/08/2024
11	A	24/08/2024
12	A	25/08/2024
13	A	26/08/2024
14	D	31/03/2023
15	A	19/06/2023
16	B	13/07/2023
17	C	30/10/2023
18	D	11/01/2024
19	D	26/05/2023
20	E	05/09/2023
21	A	14/09/2023
22	B	02/01/2024
23	D	19/04/2023

Let's define PREMIUM CATEGORY a CATEGORY that occurs in the table a number of time ≥3 (e.g. in the table above, the CATEGORY named A is a PREMIUM CATEGORY while the CATEGORY named E is not a PREMIUM CATEGORY).
In addition, I need PREMIUM CATEGORY to be affected by the selection of an interval of dates (e.g. D is a PREMIUM CATEGORY with no date filter, while if I select the interval 01/01/2024 - 31/12/2024 then D is not a PREMIUM CATEGORY).

I managed to achieve this first goal with the following measure: (assigning 1 to PREMIUM CATEGORY and 0 otherwise)

           • PREMIUM_CATEGORY = CALCULATE(IF(DISTINCTCOUNT('TBL'[ID])>=3,1,0))

Here some visuals that shows the examples I described formerly: 

Here comes the problem: what I would like to achieve, is to produce a percentage of the PREMIUM_CATEGORY over the total count of CATEGORY and show it in a proper visual. This percentage must be affected by the date interval selection too!
So, in the first case, the percent should be 4/5 = 80% while in the second case it should be 3/4 = 75%.

I hope someone can help me, thank you so much in advantage

F.

[Daniel29195 · Accepted solution]

@FabioF 

from my understanding, and please correct me if im wrong, 

if exmaple, you have 4 categories as premium --> 4 /  total nb of categories 

( all of this is affected by your slicers ) 

this is your measure : 

PREMIUM_CATEGORY = CALCULATE(IF(DISTINCTCOUNT('TBL'[ID])>=3,1,0))

%  measure = 

var prem_category  =

filter(
      addcolumns(

               allselected(tbl[category_column]),

                  "@c" ,  [PREMIUM_CATEGORY]

       ),

       [@c] = 1 
)

var total_categories = allselected(tbl[category_column])

return 

divide( countrows(prem_category ) ,  total_categories , 0 )

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[Anonymous · Accepted solution]

Hi @FabioF 

For your question, here is the method I provided:

Here's some dummy data

"TBL"

Create a measure to calculate the quantity of each type and make a judgment.

PREMIUM_CATEGORY = 
    var category = 
        CALCULATE(
            COUNTROWS('TBL'), 
            FILTER(
                ALLSELECTED('TBL'), 
                'TBL'[CATEGORY] = MAX('TBL'[CATEGORY])
            )
        )
RETURN IF(category >= 3, 1, 0)

                

Create a measure to calculate percentage.

Measure = 
VAR TOTAL = 
    CALCULATE(
        DISTINCTCOUNT('TBL'[CATEGORY])
    )
VAR TOTAL_PRE = 
    CALCULATE(
        DISTINCTCOUNT('TBL'[CATEGORY]), 
        FILTER(
            ALLSELECTED('TBL'), 
            'TBL'[PREMIUM_CATEGORY] = 1
        )
    )
RETURN DIVIDE(TOTAL_PRE, TOTAL)

Here is the result.

 

Regards,

Nono Chen

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

[FabioF · Accepted solution]

Hi guys, first of all thank you very much for your answers. In the meantime I figured out a way to solve the issue.
As soon as possible I will reply and test also your suggestions and also add the details of mine!
Thanks so much again, I'll keep in touch!

[Anonymous · Accepted solution]

Hi @FabioF 

If my answer is correct, please mark it as accept as solution.

Regards,

Nono Chen

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

[FabioF · Accepted solution]

Hi guys, first of all thank you very much for your answers. In the meantime I figured out a way to solve the issue.
As soon as possible I will reply and test also your suggestions and also add the details of mine!
Thanks so much again, I'll keep in touch!

[Anonymous · Accepted solution]

Hi @FabioF 

For your question, here is the method I provided:

Here's some dummy data

"TBL"

Create a measure to calculate the quantity of each type and make a judgment.

PREMIUM_CATEGORY = 
    var category = 
        CALCULATE(
            COUNTROWS('TBL'), 
            FILTER(
                ALLSELECTED('TBL'), 
                'TBL'[CATEGORY] = MAX('TBL'[CATEGORY])
            )
        )
RETURN IF(category >= 3, 1, 0)

                

Create a measure to calculate percentage.

Measure = 
VAR TOTAL = 
    CALCULATE(
        DISTINCTCOUNT('TBL'[CATEGORY])
    )
VAR TOTAL_PRE = 
    CALCULATE(
        DISTINCTCOUNT('TBL'[CATEGORY]), 
        FILTER(
            ALLSELECTED('TBL'), 
            'TBL'[PREMIUM_CATEGORY] = 1
        )
    )
RETURN DIVIDE(TOTAL_PRE, TOTAL)

Here is the result.

 

Regards,

Nono Chen

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

[Daniel29195 · Accepted solution]

@FabioF 

from my understanding, and please correct me if im wrong, 

if exmaple, you have 4 categories as premium --> 4 /  total nb of categories 

( all of this is affected by your slicers ) 

this is your measure : 

PREMIUM_CATEGORY = CALCULATE(IF(DISTINCTCOUNT('TBL'[ID])>=3,1,0))

%  measure = 

var prem_category  =

filter(
      addcolumns(

               allselected(tbl[category_column]),

                  "@c" ,  [PREMIUM_CATEGORY]

       ),

       [@c] = 1 
)

var total_categories = allselected(tbl[category_column])

return 

divide( countrows(prem_category ) ,  total_categories , 0 )

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍