cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Calculate ratio between E and B in the same column

Hello,

I would like to calculate the ratio between E and B in the same column.

 Date Type Count_Of_Type 9/20/2021 0:00 B 35 9/20/2021 0:00 D 24 9/20/2021 0:00 E 1775 9/20/2021 0:00 S 1710 9/21/2021 0:00 B 45 9/21/2021 0:00 D 9 9/21/2021 0:00 E 2246 9/21/2021 0:00 S 937 9/22/2021 0:00 B 55 9/22/2021 0:00 D 9 9/22/2021 0:00 E 2020 9/22/2021 0:00 S 1495 9/23/2021 0:00 B 47 9/23/2021 0:00 D 9 9/23/2021 0:00 E 1927 9/23/2021 0:00 S 885 9/24/2021 0:00 B 28 9/24/2021 0:00 D 6 9/24/2021 0:00 E 1735 9/24/2021 0:00 S 981

So for each day, a ratio is desired.

Can someone help me out with this?

11 REPLIES 11
Super User

Hello there @WLFRD !

``````ratio =
var _e = CALCULATE(COUNT(Table[Type]), FILTER(Table, Table[Type] = "E"))
var _b = CALCULATE(COUNT(Table[Type]), FILTER(Table, Table[Type] = "B"))

return
DIVIDE( _e, _b)``````

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Helper III

Thanks for your reply! This is the return (after skipping out the S and D):

 Date Type Count_Of_type ratio 9/20/2021 0:00 B 35 0.675583141 9/20/2021 0:00 E 1775 34.26171645 9/21/2021 0:00 B 45 0.868606896 9/21/2021 0:00 E 2246 43.3531353 9/22/2021 0:00 B 55 1.061630651 9/22/2021 0:00 E 2020 38.99079844 9/23/2021 0:00 B 47 0.907211647 9/23/2021 0:00 E 1927 37.19567753 9/24/2021 0:00 B 28 0.540466513 9/24/2021 0:00 E 1735 33.48962144

I was expecting:

 Date Type Count_Of_Type Ratio 9/20/2021 0:00 B 35 9/20/2021 0:00 E 1775 0.0197 9/21/2021 0:00 B 45 9/21/2021 0:00 E 2246 0.0200 9/22/2021 0:00 B 55 9/22/2021 0:00 E 2020 0.0272 9/23/2021 0:00 B 47 9/23/2021 0:00 E 1927 0.0244 9/24/2021 0:00 B 28 9/24/2021 0:00 E 1735 0.0161

35/1775 = 0.0197

So I don't know what goes wrong.

Best regards.

Super User

@WLFRD , I think that what you want is this then:

``````ratio =
var _e = CALCULATE(COUNT(Table[Type]), FILTER(Table, Table[Type] = "E"))
var _b = CALCULATE(COUNT(Table[Type]), FILTER(Table, Table[Type] = "B"))
var _selected = SELECTEDVALUE(Table[Type])

return
IF( _selected = "E",
DIVIDE( _b, _e))``````

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Helper III

For some reason the added 'selected' statement IF( _selected = "E") is not recognized bij Power BI.

Would there another solution to calculate the ratio between B and E per day?

Super User

@WLFRD what is the error message you receive?

Helper III

I would like to have the ratio per day. Now it seems it calculates the ratio for the entire column.

Community Support

Hi @WLFRD

You want a measure or a column to get the ratio? If you want a measure, you need to put date column and ratio measure into the same visual. Then the date column will provide a date filter to the ratio measure.

Or you could try this measure:

``````Ratio =
var _b = CALCULATE(SUM('Table'[Count_Of_Type]),ALLEXCEPT('Table','Table'[Date]),'Table'[Type]="B")
var _e = CALCULATE(SUM('Table'[Count_Of_Type]),ALLEXCEPT('Table','Table'[Date]),'Table'[Type]="E")
var _selected = SELECTEDVALUE('Table'[Type])
return
IF( _selected = "E",DIVIDE( _b, _e))``````

Best Regards,
Community Support Team _ Jing

If this post helps, please Accept it as Solution to help other members find it.

Helper III

Thanks for your reply! This seems a way to calculate the ratio. I just have a small adition to this. Suppose we don't have the count column and this is the data we have:

 Date Type 9/20/2021 B 9/20/2021 B 9/20/2021 B 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 S 9/20/2021 S 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 S 9/20/2021 S 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 B 9/20/2021 B 9/20/2021 B 9/20/2021 B 9/20/2021 S 9/20/2021 S 9/21/2021 E 9/21/2021 E 9/21/2021 E 9/21/2021 E 9/21/2021 B 9/21/2021 B 9/21/2021 B 9/21/2021 E 9/21/2021 E 9/21/2021 S 9/21/2021 S 9/21/2021 S 9/21/2021 S 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 B 9/22/2021 B 9/22/2021 B 9/22/2021 E 9/22/2021 E 9/22/2021 S 9/22/2021 S 9/22/2021 S 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 B 9/22/2021 B 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 B 9/23/2021 B 9/23/2021 S 9/23/2021 S 9/23/2021 S 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 B 9/23/2021 B 9/23/2021 S 9/23/2021 S 9/23/2021 B

The B and E have to be counted per day and based on this count, the ratio has to be calculated.

How do you add this count to the DAX calculation?

Community Support

Hi @WLFRD

It is similar. Create two measures.

Count of Type = COUNT('Table'[Type])

Ratio =
var _b = CALCULATE([Count of Type],ALLEXCEPT('Table','Table'[Date]),'Table'[Type]="B")
var _e = CALCULATE([Count of Type],ALLEXCEPT('Table','Table'[Date]),'Table'[Type]="E")
var _selected = SELECTEDVALUE('Table'[Type])
return
IF( _selected = "E",DIVIDE( _b, _e))

BR,
Jing
Helper III

Thanks for your help! This worked for me but I do have a question.... suppose I don't have the Count_of_type colomn so I have to count per day the "B" and "E" Type, what will then be the calculation?

 Date Type 9/20/2021 B 9/20/2021 B 9/20/2021 B 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 S 9/20/2021 S 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 S 9/20/2021 S 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 E 9/20/2021 B 9/20/2021 B 9/20/2021 B 9/20/2021 B 9/20/2021 S 9/20/2021 S 9/21/2021 E 9/21/2021 E 9/21/2021 E 9/21/2021 E 9/21/2021 B 9/21/2021 B 9/21/2021 B 9/21/2021 E 9/21/2021 E 9/21/2021 S 9/21/2021 S 9/21/2021 S 9/21/2021 S 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 B 9/22/2021 B 9/22/2021 B 9/22/2021 E 9/22/2021 E 9/22/2021 S 9/22/2021 S 9/22/2021 S 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 E 9/22/2021 B 9/22/2021 B 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 B 9/23/2021 B 9/23/2021 S 9/23/2021 S 9/23/2021 S 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 E 9/23/2021 B 9/23/2021 B 9/23/2021 S 9/23/2021 S 9/23/2021 B

Much appreciated!

Helper III

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors