Frequency calc / Histogram

[dapling]

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!

[v-jiascu-msft]

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!
Dale

[dapling]

Sorry 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.

[v-jiascu-msft]

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

[dapling]

Hi Dale, I really appreciate your help, but I'm afraid this is still not what I'm trying to achieve.

The table needs to be capable of identifying that there is a single instance (occurence) of a site (owned by PQR) which has been visited FIVE times, none visited FOUR times, 1 visited THREE times (also PQR's site), 1 site visited TWICE (owned by MNO) and 4 sites visited ONCE (2 owned by MNO and 2 by PQR).

Freq * Site Count 

[5*1] + [4 *0] + [3*1] + [2*1] + [1*4] = 14.

i.e. 14 visits in total to 7 different sites, BUT split by two different owners.

The Cust id is no longer relevant for this slice.

Confusing... Argh!!!  I've saved my amended file in the same shared place as above.

Thanks again, David

[v-jiascu-msft · Accepted solution]

@dapling

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

[CahabaData]

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      1

[dapling]

It'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

[v-jiascu-msft]

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

[dapling]

Genius, Dale! Thanks.

I've now been asked to provide a slightly different view, which has equally confused me...

Instead of counting frequency by Customer, I need to summarise by Site Owner.  We manage multiple sites, some of which we own and some on behalf of other companies.

The OwnerID is held in a separate table which is linked via a relationship on 'SiteID' in table 'VISIT' to table 'OWNER'. 

Therefore, in illustration I need:

Any more help you can provide would be much appreciated!

Thanks a lot.

[v-jiascu-msft]

Hi @dapling,

You are welcome. You can create a Column Chart. Besides, I can't see any difference between your two posts. Could you please share your PBIX file? Then I could do more.

Best Regards!

Dale