Forum Discussion
Frequency calc / Histogram
Hi, I am new to PowerBI and have a (hopefully) simple question about how to create a frequency table and ultimately a histogram.
My data is laid out as follows:
VisitID SiteID CustID
1 123 ABC
2 345 ABC
3 345 ABC
4 345 ABC
5 678 XYZ
6 345 XYZ
etc...
So ABC has performed 4 visits to two different sites (123 and 345) and XYZ has performed 2 visits to two different sites (345 and 678).
I want to be able to count the frequency of visits to unique sites (SiteID) for customers ABC and XYZ as follows:
ABC XYZ
Freq #visits #visits Total #visits
1 1 2 three times to the same site on only occasion, one for ABC & two for XYZ
2 0 0 no visits to the same site twice, by either customer
3 1 0 once to the same site three times, by one of the customers (ABC)
Thanks in advance!
Hi David,
Delete the column "CustID" from Summary. I found that the visual is correct now. Please check the file here: https://1drv.ms/u/s!ArTqPk2pu-BkgSqND4imKbOYtOeh
Summary = SUMMARIZE ( 'Visits', 'Visits'[Site id], "Fres", COUNT ( Visits[Site id] ) )Best Regards!
Dale
10 Replies
I don't see the logical construct for the resulting grid that you propose. It is not clear how the Freq column is logically defined.
Readily available would be:
SiteID CustID Visits
123 ABC 1
345 ABC 3
678 XYZ 1
345 XYZ 1It's a frequency table... My column headings were perhaps not clear... The figures are the count of the number of 'occurrences' rather than the number of visits. The table reconciles to 6 visits as follows - [(1+2)]*1] + [(0+0)*2] + [(1+0)*3] = 6 visits.
ABC XYZ
Freq #occur #occur Total #visits
1 1 2 three times to the same site on only occasion, one for ABC & two for XYZ
2 0 0 no visits to the same site twice, by either customer
3 1 0 once to the same site three times, by one of the customers (ABC)
1 Visited a single site on ONE occasion only - ABC - 1 occurrence visited site 123 , XYZ - 2 occurrences visited sites 123 and 345 = 3 visits in total
2 Visited a single site TWICE - none
3 Visited a single site on THREE occasions - ABC - 1 occurrence visited site 345 three times
Hope that helps explain! The DAX logic is beyond me... I could probably do it in Excel, using countif but the database is 100's of thousands lines long so teh spreadsheet would be slow and the ability to cut and dice the results (by period, customer, etc.) would be limited without a lot of manual effort.
Cheers
Hi dapling,
The frequency is dynamic and the possible frequency is also dynamic. So there could be more steps to achieve your goal.
1. Create a summary table.
Summary = SUMMARIZE ( 'Source', 'Source'[CustID], 'Source'[SiteID], "Fres", COUNT ( Source[SiteID] ) )2. Create a table PotentialFreqs.
PotentialFreqs = GENERATESERIES ( MIN ( 'Summary'[Fres] ), MAX ( 'Summary'[Fres] ), 1 )
3. Establish relationship between PotentialFreqs and Summary.
4. Create a measure.
numberOfFreqs = VAR nFreqs = COUNT ( 'Summary'[Fres] ) RETURN IF ( ISBLANK ( nFreqs ), 0, nFreqs )5. Import a custom visual "Histogram".
6. Create visual.
Please check this file for details: https://1drv.ms/u/s!ArTqPk2pu-BkgReCPUHxVhzv7-Cr
Best Regards!
Dale
Hi dapling,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
DaleSorry it's taken so long for me to get back to this...
I have shared the illustration file, with the second Owner table.
https://1drv.ms/f/s!ApwUJrTj9hAldFElMq8P1GBq-KM
Thanks a lot.
Hi dapling,
1. Update the relationships as the picture showed.
2. Create the visuals like this:
You can check it out in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgSn1uv0UV2PlYlo2.
Best Regards!
Dale
