count how many times based on distinct values.

[Anonymous]

Hi All, 

I have my table which has customers IDs (It's originally distinct values, but IDs are repeated for the value per every month and defined by other characteristics (I unpivoted the table) so simply it is like this; 

ID | Month | Value

11 | Jan | 0 

11 | Feb | 100 

11 | Mar | 120

... etc

Second customer

ID | Month | Value 

12 | Feb | 90

12| Mar | 100 

12 | Apr | 100

Third customer 

ID | Month | Value

15 | Feb | 100

15 | Mar | 100 

15 | Dec | 0

of course, it is one table for all customers. I want to calculate;

how many customers have for example 100 as a value and how many times based on month;

1 customer (ID11): 1 time 

2 customer (ID12 and ID15): 2 times 

 I want the final result as a table like this:-

Never had 100: one time: two times: three times: four times:.... etc: 12 times 

12 customers: 6: 12: etc ... 

I hope my question is clear^^

[Zubair_Muhammad · Accepted solution]

@Anonymous

See file attached as well

[Anonymous]

I would be glad if I knew if this is even possible or not at least. 

Thank you.

[Zubair_Muhammad]

HI @Anonymous

Try this

First Add a calculated Column

No.of.Times =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLEXCEPT ( Table1, Table1[ID ] ), [ Value] = 100 )
)

and this MEASURE

Name of Customers =
CONCATENATEX ( VALUES ( Table1[ID ] ), [ID ], ", " )

Now in Table Visual drag the above calculated column and measure and disctinct count of IDs

[Zubair_Muhammad · Accepted solution]

@Anonymous

See file attached as well

[Anonymous]

Hi @Zubair_Muhammad

Thank you very much. I am going to try it on my data base, I hope it will work!

[Anonymous]

Hi @Zubair_Muhammad

I don't understand how did you arrive at [count of ID]?

because I did [no. of. times] and the [name of customers] but I frankly don't need the name of customers (I actually can't use it in visulaization given the huge dataset) but I want the [count of ID].

Can you help me? I even tried to re do it in the power bi you uploaded but still doesn't work. 

Thanks,

[v-lili6-msft · Accepted solution]

hi, @Anonymous

[count of ID] is Aggregates in Power BI visualizations.

https://docs.microsoft.com/en-us/power-bi/service-aggregates

You could also use DISTINCTCOUNT Function to create a measure instead of it.

Measure = DISTINCTCOUNT(Table1[ID ])

Result:

Best Regards,

Lin

[Anonymous]

@v-lili6-msft @Zubair_Muhammad

It totally worked now. Many Thanks^^

[Zubair_Muhammad]

I dont have my laptop now
But i remember it was a simple distinctcount of IDs

[Anonymous]

@Zubair_Muhammad

That's what I tried to do, I did = countrows(distict(table [ID])) but then I would have 33 and 81 which is the summation of counts 😞

Thanks,