Calculate the percentage by date and name

[Anonymous]

Hello! 

I'm a complete newbie in Power BI, so maybe this question is super easy to answer...

I have two tables. Table 1 "Total" shows how often the regarding "location" was used on different dates.

The second table "Errors" shows every event, when using the location didn't work out.

Now I want to calculate the percentage of how often entering the specific location on each date was successful.

See simplified data below:

Table "Total":

Table "Errors"

 

Desired result:

I already made measures for each table which returns the total by date and location:
measure1 = calculate ( sum ('total'[Number]) , filters ('total'[Date]) , filters ('total'[Location] ) )

measure2 = calculate ( count ('errors'[Location]) , filters('errors'[Date] )

I tried to make a new measure where I simply divided those two, but I only got one value in return, so not as easy as I thought... 

I would be really thankful if someone could help me out!

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

I have done it now in DAX.

See my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJi1wXA5gXUQq7DC-L?e=JgCXC9

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @mangaus1111

I can recreate what you did in DAX, but every slot is filled with 100% for me. If I only return Count_Errors it is one number and not an array of numbers. If I return [SumNumber] - Count_Errors, it seems like Count_Errors is 0 everywhere. Do you have a hunch where the problem could be?
Thanks so much!!

[mangaus1111]

Hi @Anonymous ,

in your visual you have to use columns [Date] and [Location] from the table 'Total'.

Otherwise I don´t know what other problem is.

If you can send me your pbi file I can better investigate it.

[mangaus1111]

Hi @Anonymous ,

Hi @Anonymous ,

see my pibi file.

I have used Group By and Merge functions in Power Query.

https://1drv.ms/u/s!Aj45jbu0mDVJi1kk6xzm3fPMni5A?e=oMyCOs

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@mangaus1111  Thanks for the quick answer!! 
My "total" table is a calculated table because I had to union different data sets into one (see picture of raw data, just for clarification). So sadly it doesn't show in Power Query...could I fix that?
Or do you maybe know another way to calculate it?

the raw data looked like this: