Fining a Percentage

[JohnW]

Hi everyone,

I am having touble getting this to work and I'm not quite sure how to word this question but here it is.  I am trying to get a percentage to show how many people used thier passes throughout the year based off of what pass they bought.  The passes are 25, 50, 75 and 100 swipes. 

So for example the first one is the 25 pass.  The total is 403 because I have it filtered to only show the people that didn't use more than half of thier swipes.  The grand total for 25 passes sold was 801.  I'd like to get something that shows the percentages between these two totals while still being able to filter out each pass.  So it should be around 50% for the 25 swipe pass, 54% for the 50 pass, 64% for the 75 pass and 58% for the 100 pass.

Thanks for the help!

[v-alq-msft · Accepted solution]

Hi, @JohnW 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

Table1, Table2, Table3, Table4:

You may create four measure as below.

Visual Control 1 = 
IF(
    SELECTEDVALUE(Table1[Pass 25])>=25*0.5,
    1,0
)

Visual Control 2 = 
IF(
    SELECTEDVALUE(Table2[Pass 50])>=50*0.54,
    1,0
)

Visual Control 3 = 
IF(
    SELECTEDVALUE(Table3[Pass 75])>=75*0.64,
    1,0
)

Visual Control 4 = 
IF(
    SELECTEDVALUE(Table4[Pass 100])>=100*0.58,
    1,0
)

Finally you need to put the measure in the corresponding visual level filter to get the filtered result.

Best Regards

Allan

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

[MFelix]

Hi @JohnW ,

Can you please elaborate a little more on what you need to get? The 25 and the 801 are what values? Count of id's? Sum of pass?

Can you share a sample data and expected result.

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

[JohnW]

Hi @MFelix ,

The 25, 50, 75 and 100 are the number of swipes a customer can buy for the year.  The 801 is the sum of passes in the 25 swipe passes bought for the year.  The 403 is the sum of passes in the 25 swipe passes but filtered down to customers who didin't use over half of thier passes in the year.

25 Swipe Pass 

Customer ID	Number of Passes left
1234	25
1235	20
1236	19
1237	15
1258	13
Total	92

Total amount of 25 swipe passes bought 200

92/200 = 46% 

[MFelix]

Sorry for the question once again and how do you now the number of passes you have with 25?

Is it based on the number of ID's multiplied by 25?

Can you share the data but with also the information about the rest of the passes because you show how many have swipes left but you must have lines with 0 correct?

DO you have any column that identifies the passes?

You should do something similar to this:

Percentage = SUM(Table[Number of Passes Left]) / calculate(SUM(Table[Number of passes bought); all(Table))