Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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^^
Solved! Go to Solution.
@Anonymous
See file attached as well
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
I would be glad if I knew if this is even possible or not at least.
Thank you.
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
@Anonymous
See file attached as well
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,
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
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |