[HELP] Need to use a value of a measure to count distinct values

[Anonymous]

Hello Guys, 

I'm creating a report that calculate the frequency of purchases of a client, and i got 6 categories:

F1 - Client bought 1x time for the last 6 months ( 1 Month )

F2 - Client bought 2x time for the last 6 months ( 2 Months )
F3 - Client bought 3x time for the last 6 months ( 3 Months )

F4 - Client bought 4x time for the last 6 months ( 4 Months )

F5 - Client bought 5x time for the last 6 months ( 5 Months )
F6 - Client bought 6x time for the last 6 months ( 6 Months )
F0 - Client didn't buy anything for the last 6 months

At first i created a Calculated Table that worked just fine, but i couldn't use dinamic dates with calculated table so i created a measure like this:

Medida Fat 1 =

Var Data1 = IF(

HASONEFILTER(dCalendario[AnoMês]);

DATE(

LEFT(VALUES(dCalendario[AnoMês]);4);

RIGHT(VALUES(dCalendario[AnoMês]);2);01);

DATE(YEAR(TODAY());MONTH(TODAY());01))

Var Fat1 = IF(

CALCULATE(SUM(iBaseDados[Qtde.Fat.S609])+SUM(iBaseDados[Carteira total]);

FILTER(ALL(dCalendario[Data]);dCalendario[Data] = Data1))>0;1;0)

Var Fat2 = IF(

CALCULATE(SUM(iBaseDados[Qtde.Fat.S609]);

FILTER(ALL(dCalendario[Data]);dCalendario[Data] = EDATE(Data1;-1)))>0;1;0)

Var Fat3 = IF(

CALCULATE(SUM(iBaseDados[Qtde.Fat.S609]);

FILTER(ALL(dCalendario[Data]);dCalendario[Data] = EDATE(Data1;-2)))>0;1;0)

Var Fat4 = IF(

CALCULATE(SUM(iBaseDados[Qtde.Fat.S609]);

FILTER(ALL(dCalendario[Data]);dCalendario[Data] = EDATE(Data1;-3)))>0;1;0)

Var Fat5 = IF(

CALCULATE(SUM(iBaseDados[Qtde.Fat.S609]);

FILTER(ALL(dCalendario[Data]);dCalendario[Data] = EDATE(Data1;-4)))>0;1;0)

Var Fat6 = IF(

CALCULATE(SUM(iBaseDados[Qtde.Fat.S609]);

FILTER(ALL(dCalendario[Data]);dCalendario[Data] = EDATE(Data1;-5)))>0;1;0)

Var Soma = Fat1+Fat2+Fat3+Fat4+Fat5+Fat6

Return

SWITCH(Soma;

0;"F0";

1;"F1";

2;"F2";

3;"F3";

4;"F4";

5;"F5";

6;"F6")

And worked but now i want to COUNT the number os clients F0,F1,F2,F3,F4,F5 and F6 and i can't think a way to use a measure to count how much "measures" are equal to "F0" or "F1"

The Measure Medida Fat 1 Returns This Table:

Parceiro Principal	Medida Fat 1
123456	F0
123457	F1
123458	F0
123459	F0
123460	F2
123461	F3
123462	F0
123463	F1
123464	F5
123465	F6
123466	F1
123467	F1
123468	F4
123469	F4
123470	F0

IF i could create sort of a "Virtual Table" And then count the values, can i do that?

Please help me

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Coud you kinldy ensure that in the PBI file, your Table and column names are in English.  Thank you.

[v-piga-msft]

Hi @Anonymous ,

Do you want to get the count below?

If it is, please create the measure with the formula below.

Measure =
VAR a =
    SUMMARIZE ( 'Table1', 'Table1'[Parceiro Principal], "Medida", [Medida Fat 1 ] )
RETURN
    CALCULATE ( COUNTROWS ( a ) )

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Anonymous]

Thanks for the Help but you are considering that "Medida Fat 1" Is a column, and it's a measure so when i do this, it only count the total of rows and i need the total of each category ( F0, F1, F2, F3, F4, F5, F6 )

I upload a excel DataBase in this Link 
https://ufile.io/byykzecs

[Ashish_Mathur]

Hi,

Is this the answer you are expecting?  If no, then please let me know what exact results you want for each bucket.

[Anonymous]

Yes, i need that results plus the F0 clients, whose are the clients who didn't buy anything from the last 6 months

Can you tell me how you did it please?

[Ashish_Mathur]

Have you cross checked all those numbers?