Average of result grouped by dates

[perplexedDev]

I have a table of the format

Date    Result

4/11     F

4/11     F

4/12     P

4/12     F

4/12     P

I need to get the average of pass and fail records per date to plot a chart as below

Date    PassAvg   FailAvg

4/11     0               100%(i.e,2*100/2)

4/12     66.6%       33.3% 

How can I achieve this?

[Sean · Accepted solution]

@perplexedDev

There's 2 ways to do this. I'll give you both because inevitably someone will read this and try to give you the easy way...

First (not the way I would do it - but its faster)

You need to write only 1 Measure - and create a Matrix - place fields as in the picture!

Second you have to write 5 simple Measures but it gives you more flexibilty later on... for other visualiazations including charts

I may post a chart later which you can't do with the first method

Hope this helps!

[calerof]

Hi,

But what about the dates grouping, in this case by day?

Fernando

[perplexedDev]

The secod solution works perfectly. I had one question though, how did you add the percentage sign for Avg measures?

[Sean]

Modeling Tab => Format: => select Percentage

[perplexedDev]

Thank you so much. Sorry for my ignorance,I am new to excel and power BI.