Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculations by date by sample

Hello! I'm am extremely green to Power BI so I thought I'd get my feet wet with creating a simple table for the user. However, it isn't turning out to be as easy as I thought due to the date field. I've searched over 20 posts on the forum and have tried several ways.

This is all I want to do:

1. We take samples at 20 sites once a week, however not all in one day.

2. I'd like to be able to do the following for each site that was sampled per day:  average of the material sampled, count of the material sampled, Max of Material sampled, Min of material sampled.

I created a second table with the calculations for #2 but the date is still showing up separately and I only get one number for each of the calculations. This is the calculation I used for the second table.

SampleCalculations = SUMMARIZECOLUMNS(GROUPBY('SystemResiduals-Water','SystemResiduals-Water'[Date]),"Daily CL2 High", MAX('SystemResiduals-Water'[Chlorine]),"Daily CL2 Low",MIN('SystemResiduals-Water'[Chlorine]),"Daily CL2 Average",AVERAGE('SystemResiduals-Water'[Chlorine]),"Daily CL2 Sample Count",COUNT('SystemResiduals-Water'[Chlorine]),"Daily pH High",MAX('SystemResiduals-Water'[PH]),"Daily pH Low",MIN('SystemResiduals-Water'[PH]),"Daily pH Average",AVERAGE('SystemResiduals-Water'[PH]),"Daily pH Sample Count",COUNT('SystemResiduals-Water'[PH]))

  I've spent over half the day trying different ways to achieve the results I want. This is my last resort and I hope someone can help.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Never mind, apparently Power BI started to behave and did what I wanted it to do..🙄

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Never mind, apparently Power BI started to behave and did what I wanted it to do..🙄

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    4 years ago

    Hi Anonymous ,

     

    In order to get your question answered quickly please provide the following:

    - Sample Data as a table pasted into your message

    - Expected outcome in a visual or table

    Thanks,

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi Anonymous,

     

    Welcome to the community.

    You'll definitely get the answer you need here. 

    Can you post some sample data or a sample PBIX file? (nothing sensitive obviously)

    There is some good info in Greg_Deckler post here, https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1964858#M744826 with links to the best ways to post sample data if you need help with that.

     

    Also, this site https://www.daxformatter.com/ is great if you want a quick way to format/validate your DAX.

    It's awesome, copying and pasting your code and clicking one button results in this.

    SampleCalculations =
SUMMARIZECOLUMNS (
    GROUPBY (
        'SystemResiduals-Water',
        'SystemResiduals-Water'[Date]
    ),
    "Daily CL2 High", MAX ( 'SystemResiduals-Water'[Chlorine] ),
    "Daily CL2 Low", MIN ( 'SystemResiduals-Water'[Chlorine] ),
    "Daily CL2 Average", AVERAGE ( 'SystemResiduals-Water'[Chlorine] ),
    "Daily CL2 Sample Count", COUNT ( 'SystemResiduals-Water'[Chlorine] ),
    "Daily pH High", MAX ( 'SystemResiduals-Water'[PH] ),
    "Daily pH Low", MIN ( 'SystemResiduals-Water'[PH] ),
    "Daily pH Average", AVERAGE ( 'SystemResiduals-Water'[PH] ),
    "Daily pH Sample Count", COUNT ( 'SystemResiduals-Water'[PH] )
)

     

    With some data to work with, I think it should be easy enough to solve.

     

    Regards,

    Kim

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      KNP  Thank you so much for the advice! I will remember for next time, which I'm sure there will be ğŸ˜€