Calculate ratio between all rows of a table with filter applied and a count of a column

[Simone2004]

Hi everyone,

I hope I am clear.

I need to create a new measure. This  measure should be a division between all rows of a table / COUNT(MyTable[MyColumn]).

The count of rows of the table should change according the filters user apply on the page report. I dont know which filter the user could  apply.

I try to do an example. I have this table:

    Col1   |     Col2          |       Col3     | New Measure I need to calculate 

     A      |        A             |        10       |                   5 / 10 = 0.5     

     A      |        B             |        20       |                   5 / 20 = 0.25

     A      |        C             |        40       |                   5 / 40 = 0.125

     B      |        A             |        70       |                   5 / 70 = ....

     B      |        B             |        80       |                   5 / 80 = ....

Now user decide to apply a page filter.... for example Col1 = A.... The table should become: 

   Col1   |     Col2          |       Col3     | New Measure I need to calculate 

     A      |        A             |        10       |                   3 / 10

     A      |        B             |        20       |                   3 / 20 

     A      |        C             |        40       |                   3 / 40 

Then user decide to apply a new page filter.... for example Col1 = A AND Col2 = B.... The table should become: 

   Col1   |     Col2          |       Col3     | New Measure I need to calculate 

     A      |        B             |        20       |                   1 / 20 

Finally user decide to delete all filters and apply a new one... for example just Col2 = B

    Col1   |     Col2          |       Col3     | New Measure I need to calculate 

     A      |        B             |        20       |                   2 / 20 = ....

     B      |        B             |        80       |                   2 / 80 = ....

A note: Col3 is already summarize as count....

Can anyone help me please?

Thank you

[Jihwan_Kim]

Hi, @Simone2004 

Please check the below picture whether it is what you are looking for.

The link to the sample pbix file is down below.

New Measure =
DIVIDE (
CALCULATE ( COUNTROWS ( Data ), ALLSELECTED ( Data[Col1], Data[Col2] ) ),
SUM ( Data[Col3] )
)

https://www.dropbox.com/s/7v60vqqjhkkjrrx/simone.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Simone2004]

mmm... I am sorry... perhaps the information I give in the post are not sufficient...

Your solution works perfectly, but when I extend it to my case, something get broken out...

I explain the problem again...

Col1 and Col2 are keys of another table... exactly they are countries... so, I have the `table_countries` in this way:

   key      |      name

    A        |    portugal

    B        |       spain 

    ....       |          ...

Then I have the table I let you see above.... 

Well, if I build a report as the example above, using country keys, everything works (left in the image on the bottom); but if I use name of countries instead of key, it does not work anymore (center)... if you see, it's like the relationship between the countries and my table get broken.... but if I remove the measure, the relationship comes back to work (right):

What's wrong?

[Jihwan_Kim]

Hi, @Simone2004 

Thank you for your feedback.

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thank you.

[Simone2004]

Hi,

 thank you for your help. I shared my data with you in pvt message, but then we can continue here.

[Jihwan_Kim · Accepted solution]

Hi, @Simone2004 

Please check the link down below.

I created new tables for departure country and arrival country. Because the existing ones are having too many rows. And if it has the function all or allselect, then the calculation might become slow.

https://www.dropbox.com/s/fvh3jiv5h8ztr7o/requests.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Simone2004]

Thank you. 
I have merged your pbix with the formula you gave me on previous reply. Now it works.

[selimovd]

Hey @Simone2004 ,

try the following measure:

New Measure I need to calculate =
DIVIDE(
    COUNTROWS( MyTable ),
    SUM( myTable[Col3] )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Simone2004]

I splitted your measure in 2 parts to see the result of each operation... This is the result for `COUNTROWS(myTable)`:

I would expect that `COUNTROWS(myTable)` was equals for each row.... it changes instead...