Calculating daily peak and peak and slice result based on group and sub group

[Ikeumlaut]

Hey,

I have data from different surveys. The surveys last about 2 weeks and survey data is collected 1-4 times per day. The survey has multiple groups and sub groups that are observed durng the survey.

Here is an example how my data looks like.

I want to do two things with the data in Power BI, analyze it by survey and then have a "benchmark" of all the surveys.

To analyze it by survey I want to calculate the average, daily peak average, and peak of fruits/vedgetables that are ok (ripe and underripe) in the survey and have the slicers work for category and sub category. See picture

It works for the average calculation (SurveyAvg = CALCULATE(DIVIDE(AVERAGE('Sheet1 (2)'[Ripe and Underripe]);(AVERAGE('Sheet1 (2)'[Category Sampe Size])));ALLSELECTED('Sheet1 (2)')) but I cannot get it to work for peak or avg daily peak. The peak works fine (SurveyPeak = CALCULATE(DIVIDE(MAX('Sheet1 (2)'[Ripe and Underripe]);AVERAGE('Sheet1 (2)'[Category Sampe Size]));ALLSELECTED('Sheet1 (2)'))) if I have a sub category selected but it does not calculate correctly for all Apples or all Paprikas or all Paprikas and Apples. Peak is calculated by taking the peak of ripe+underripe in the survey and dividing it by the sample size. I think the problem is that my formula does not sum the sub categories together when when I want the result for all Apples or all Paprikas etc. So it should sum the rows together for red and white apples if I have in the slicer only Apples selected and no sub categories. I've tried to use summarize function in the the formula but does not work or I'm using it incorrectly.

Avg daily peak should be calculated by finding out the peak for each day and then taking an average of those. What formula returns the max for a single day?

Please help! Thank you

-Ike

[Vvelarde]

hi @Ikeumlaut

Try Replacing:

ALLSELECTED('Sheet1 (2)')))

by:

ALLEXCEPT(Sheet1(2); Sheet1(2)Survey; Sheet1(2)Category)

[Ikeumlaut]

Hey @Vvelarde

thanks for the suggestions, but that did not work. In addition now when I select a subcategory the formula does not calculate the peak correctly. This worked with ALLSELECTED as a filter.

-Ike

[Vvelarde]

Hi, don't understand very clear of what are you trying to obtain in Peak Measure. Can you post a sample of what you need?.

[Ikeumlaut]

Hey,

please see attached photo where I try to explain how the peak and avg daily peak are calculated.

What I want is to have a line and stacked column chart where the amounts of underripe, ripe, and overripe are as stacked bars and then as lines the three different measures: avg, avg daily peak, and peak. See photo in my first post of the graph I want.

The Avg measure (SurveyAvg = CALCULATE(DIVIDE(AVERAGE('Sheet1'[Ripe and Underripe]);(AVERAGE('Sheet1'[Category Sampe Size])));ALLSELECTED('Sheet1'))) works correctly for all the slicers but I cannot get the peak measure to work correctly. Peak works correctly if I have a sub category selected but not when I just have Apples or Paprikas selected.  In additon, I don't know how to calculate the avg daily peak measure using DAX. 

Thank you for the help!

-Ike 

[Eric_Zhang]

@Ikeumlaut

I think you're misunderstanding the slicer. The slicers only filter the dataset but not affect the measures behavior. Measures works shared-axis(time of update) and columns series wise in the line and stacked column chart in this case, regardless of category or sub category.

To get the expected output, please post some sample data. The snapshot doesn't work as we can't copy data within it.

[Ikeumlaut]

@Eric_Zhang

here is the sample copy&pasted. I hope this is what you ment. Thank you for the help!

Survey	Time of sample	Gategory	Sub category	Category Sample Size	Amount Underripe	Amount Ripe	Amount Overripe
January test	15.1.2016 10:20	Apples	Red	30	10	15	5
January test	15.1.2016 10:20	Apples	White	25	3	20	2
January test	15.1.2016 10:20	Paprikas	Yellow	10	1	9	0
January test	15.1.2016 10:20	Paprikas	Green	5	0	3	2
January test	15.1.2016 13:00	Apples	Red	30	5	23	2
January test	15.1.2016 13:00	Apples	White	25	3	20	2
January test	15.1.2016 13:00	Paprikas	Yellow	10	1	9	0
January test	15.1.2016 13:00	Paprikas	Green	5	0	5	0
January test	16.1.2016 10:30	Apples	Red	30	2	26	2
January test	16.1.2016 10:30	Apples	White	25	1	21	3
January test	16.1.2016 10:30	Paprikas	Yellow	10	1	6	3
January test	16.1.2016 10:30	Paprikas	Green	5	1	2	2
January test	16.1.2016 13:13	Apples	Red	30	7	18	5
January test	16.1.2016 13:13	Apples	White	25	1	23	1
January test	16.1.2016 13:13	Paprikas	Yellow	10	1	8	1
January test	16.1.2016 13:13	Paprikas	Green	5	0	5	0
February test	15.2.2016 10:15	Apples	Red	15	1	11	3
February test	15.2.2016 10:15	Apples	White	10	1	5	4
February test	15.2.2016 10:15	Paprikas	Yellow	20	5	15	0
February test	15.2.2016 10:15	Paprikas	Green	25	2	20	3

-Ike

[Ikeumlaut]

I think I've got now the peak and average figured out but avg daily peak is still unsolved. Average and Peak work correctly with the slicers for Study, Category, and sub category

To calculate the average I use the following measures:

1. A measure to sum the Ripe and Underripe column by the time of the sample

   •  Ripe and Underripe SUM by Round = CALCULATE(SUM(Sheet1[Ripe and Underripe]);DISTINCT(Sheet1[Time of sample]))

2. Number of rounds or time of samples
   • Distinct Round Count = CALCULATE(DISTINCTCOUNT(Sheet1[Time of sample]);ALLSELECTED(Sheet1))
3. Number of rows
   • Number of rows = CALCULATE(COUNTROWS(Sheet1);ALLSELECTED(Sheet1))
4. Average number of sum of ripe and underripe
   • AverageRipeAndUnderripe = DIVIDE([Ripe and Underripe SUM by Round];[Distinct Round Count])
5. Average number of ripe and underripe as percentage of sample size
   • Average Utilization = CALCULATE(DIVIDE([AverageRipeAndUnderripe];[Sample Size]);ALLSELECTED(Sheet1))

To calculate the peak I use the following measures:

1. Measure to find out the max, do not really understand this but it seems to work
   • MAX Ripe and Underripe = CALCULATE(MAXX(SUMMARIZE(Sheet1;Sheet1[Time of sample];"RipeUnderripe";SUM(Sheet1[Ripe and Underripe]));[RipeUnderripe]))
2. Calculate the percentage of max from sample size
   1. Peak Utilization = CALCULATE(DIVIDE([MAX Ripe and Underripe];[Sample Size]);ALLSELECTED(Sheet1))

I've tried to calcualte the avg daily peak but I get only "Daily MAX" and "Avg Daily"

• Daily Max

  • Daily MAX = var Summarizebytimeofsample = CALCULATETABLE(SUMMARIZE(Sheet1;Sheet1[Time of sample];"Date"; DATEVALUE(Sheet1[Time of sample]);"Ripe+Underripe";CALCULATE(SUM(Sheet1[Ripe and Underripe]))))

    var dailymax = CALCULATE(MAXX(SUMMARIZE(Summarizebytimeofsample;[Date];"RipeUnderripeDailyMax";MAXX(Summarizebytimeofsample;[Ripe+Underripe]));[RipeUnderripeDailyMax]))

    return dailymax

• Avg Daily

  • Daily Avg = var Summarizebytimeofsample = CALCULATETABLE(SUMMARIZE(Sheet1;Sheet1[Time of sample];"Date"; DATEVALUE(Sheet1[Time of sample]);"Ripe+Underripe";CALCULATE(SUM(Sheet1[Ripe and Underripe]))))

    return CALCULATE(MAXX(SUMMARIZE(Summarizebytimeofsample;[Date];"RipeUnderripeDailyMax";AVERAGEX(Summarizebytimeofsample;[Ripe+Underripe]));[RipeUnderripeDailyMax]))

I've tried to add another summarize function in between to summarize the data by date and calculating the max of each date, but it does not work.

Avg of Daily Max = var Summarizebytimeofsample = CALCULATETABLE(SUMMARIZE(Sheet1;Sheet1[Time of sample];"Date"; DATEVALUE(Sheet1[Time of sample]);"Ripe+Underripe";CALCULATE(SUM(Sheet1[Ripe and Underripe]))))

var dailymax = CALCULATETABLE(SUMMARIZE(Summarizebytimeofsample;[Date];"RipeUnderripeDailyMax";MAX(Summarizebytimeofsample;[Ripe+Underripe])))

return AVERAGE(dailymax)

Please help, thanks!