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.
Hello,
I am struggling through something that I'm sure is very simple. I have data
Store | Invoice | Item |
Blue | 100 | A |
Blue | 100 | A |
Blue | 100 | A |
Blue | 100 | B |
Blue | 101 | B |
Blue | 101 | C |
Blue | 102 | A |
Blue | 103 | B |
Blue | 103 | C |
Blue | 103 | C |
Blue | 104 | A |
Blue | 104 | D |
Green | 200 | A |
Green | 200 | B |
Green | 201 | B |
Green | 201 | B |
Green | 201 | C |
Green | 201 | C |
Green | 202 | A |
Green | 203 | B |
Green | 203 | A |
Green | 204 | A |
Green | 204 | A |
Green | 204 | C |
What I would like to do, is have the frequency of each occurence pulled in. ie, Blue, 100, & A occurs 3 times, while Blue, 100, & B only once. I figured I would set up a Calculated Table to summarize, but can't get the summary to work for me. I want my Calculated table to turn out like my visual here.
My Code
Table = SUMMARIZE(Sheet1,Sheet1[Store],Sheet1[Invoice],Sheet1[Item],"Count",DISTINCTCOUNT(Sheet1[Item]))
is obviously incorrect. Please help. I have the file attached.
https://www.dropbox.com/s/jxzfbfqjm5z3y2i/Sample.pbix?dl=0
Thanks!
Solved! Go to Solution.
@Anonymous
In this case you can just replace
DISTINCTCOUNT ( Sheet1[Item] )
with
COUNTROWS ( Sheet1 )
Out of interest, why is it useful to create this as a calculated table (when you can do the same thing with a visual)?
Hi There,
You can also use GROUPBY in the Query Editor for counting the frequency of occurances in this case as shown in the screenshot.
Hi There,
You can also use GROUPBY in the Query Editor for counting the frequency of occurances in this case as shown in the screenshot.
@Anonymous
In this case you can just replace
DISTINCTCOUNT ( Sheet1[Item] )
with
COUNTROWS ( Sheet1 )
Out of interest, why is it useful to create this as a calculated table (when you can do the same thing with a visual)?
Thank you. I would like to do this so that I can filter out items that have only one frequency.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |