How to calculate % of a data in PowerBI

[iLikeAzureSQL]

Hello everyone,

My data set has values of 0 or 1 for a fact over another variable (consider shooting on target over distance)

I need to be able to graph and extract the % of successful target hits over the distance. How can I do this in PowerBI and graph ?

Data:

distance - hit

10,0

10,1

10,1

20,0

20,1

50,1

50,0

.

.

.

Results expected:

10 ft %89 sucess

20 ft %75 sucess

50 ft %54 sucess

100 ft %25 sucess

[ankitpatira]

@iLikeAzureSQL follow steps below in power bi desktop modelling using DAX.

1. First create new measure called TotalVal using below code

TotalVal = SUM( YOURTABLE[hit] )

2. Then create another measure called FinalVal using code belwo

GrandVal = CALCULATE( [TotalVal], ALL(YOURTABLE) )

3. Lastly create another measure using code below to calculate percentage share

HitPct = [TotalVal]/[FinalVal]

4. Then change data type of measure created in last step to Percentage

[iLikeAzureSQL]

This approach work, no problem with  creating measures and percentage, but the values doesn't seems to be correct! I know my data and compared to the PowerBi calucaltion, it's way off, I'll digg to see what did I do wrong

[iLikeAzureSQL]

Looks like your way is calculating percentage over the Grant total, this is not what I need!

My perpose is to find the percentage over the group, like %90 of hits from 10 ft are secessfull ( 9 out of 10 hits from 10 ft) and not %25 of hits are successful compared to total hits.

[Sean]

@iLikeAzureSQL Try these

[iLikeAzureSQL]

This seems to be easy way to go, no doubt it will work, the onlything is that my data is not aggregated, it's not grouped by the distance, so I need to find another way to get these results off the core data.

Thanks for being interested and helping

[Sean]

Why does you data need to be aggregated? by distance?

Do you have a distance column?

I'm not sure what you mean...

[iLikeAzureSQL]

I Posted my data at first, here is what it looks like

Data:

distance - hit

10,0

10,1

10,1

20,0

20,1

50,1

50,0

10,1

10.1

10,2

50,0

20,1

.

.

.

[Sean]

@iLikeAzureSQL Why don't you split the column in the Query Editor? Before you load in the Data Model

[iLikeAzureSQL]

Data is loaded, no problem there, and on seperate columns (plus many more other columns that are not related to this)

You gave me an spark now! is there any way in PowerBi to create a view based on existing data set? if yes, then I can create an aggrigated view for grouping and run my report based on view

[Sean · Accepted solution]

Just create the Measures and then a Table with the Fields organized as in Picture.

[iLikeAzureSQL]

Thank you for follow ups, the last post helped me to get to what I was looking for.