Forum Discussion

dapling's avatar
dapling
Frequent Visitor
8 years ago
Solved

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    ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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's avatar
dapling
Frequent Visitor
8 years ago

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's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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