Problem with calculating capture rate based on name

[JeroenR]

Hi all,

I cannot figure it out how I can calculate the capture rate. I have the following data structure:

Dimensions

SensorId

SensorName

ZoneId

ZonName

Location

SensorData

SensorId

Date

Number

The tables link with each other based on the "SensorId" field. 
I get stuck with the different Zones that the table "Dimensions" has. There are currently two different zones, called: "Total" and "Shop". The sensors are connected with one zone.

I have now done a simple SUM() the zone "Total" which gives me an output of 725 in the giving Power BI file of this post. This 725 is a SUM of 3 sensors. 

I also have done a SUM of the 2 shops I have, they have the following numbers: "45" and "80". I now need to get the capture rate of both shops (seperatly) divide by the total (725). I have managed to get this done with one measure, 45 / 725 * 100 = 6.2%. But this will only be displayed when I also inlcude the Sensors of the "Total" zone, which I don't want to include in my report. When I hide these sensors, there will be an capture rate of Infinity. 

I also have a measure which need to calculate the AVG of all capture rates, because my original capture rate measure does a SUM in the totals of a matrix/table. Therefore I needed to create an new measure which displays the AVG of the all Capture rates. But this measure also does not work properly. 

How do I solve this problem? Sorry for my bad English, if you have a question please feel free to ask. I have include the Power BI file. Power BI File. 

See image below, with all sensors displayed capture rate will be shown. 

[JeroenR]

Hi @v-eqin-msft

Thank you for responding to my post.

Sorry I have accidently accepted your post as a solution, but this is not quit yet what I want.

The calculations you provided me do indeed work, but I prefer not to use a 'calculated column' but a measure because I want the shop rate to be dynamic between all differsuent filters; ch as date, location 

I have created some columns with the expected outputs I hope that, with these screenshots, you understand me. I have also added another Location with some shops to it, this to make it a bit more clear to you..

This is the data of all different locations sorted by entrance:

When no filters are selected, the prefered outcome would be like this, with a measure if possible.

It shows all count data of each sensor and the shop rate for each different sensor, based on the totals of the location.

When only 1 location is selected, the shop rate stays the same, but the total will be different. This is now displays the AVG of the shop rate of this location.

This is the count data when 1 location is selected. 

With these screenshots I do not have dates included, but these do exist in my datamodel. I hope it makes a little bit more sense now to you, otherwise please let me know.

Thank you very much for all your help..!

[v-eqin-msft · Accepted solution]

Hi @JeroenR ,

Please try the following formula to create a calculated table:

Table =
ADDCOLUMNS (
    SELECTCOLUMNS ( 'Dimensions', "id", [SensorId], "name", [SensorName] ),
    "All Count",
        CALCULATE (
            SUM ( 'SensorData'[Numbers] ),
            FILTER ( 'SensorData', [SensorId] = [id] )
        )
)

Add the Shop Rate column:

Shop Rate =
VAR _sensortaotal =
    CALCULATE (
        SUM ( 'SensorData'[Numbers] ),
        FILTER ( 'Dimensions', [ZonName] = "Total" )
    )
RETURN
    IF ( CONTAINSSTRING ( [name], "Shop" ), DIVIDE ( [All Count], _sensortaotal ) )

The final output is shown below:

Here is the pbix file.

And actually I'm confused about this: I also have a measure which need to calculate the AVG of all capture rates...

Do you want  220/(220+280+225+45+80)... or 220/(220+280+225) or something else?

Please explain to me in more detail with expected outputs by providing me with a calculation formula or some screenshots.Thanks😀

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