Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thanks in advance.
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)
Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
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
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.
@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))
Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
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
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?
Thanks in advance.
I would like to have the ratio per day. Now it seems it calculates the ratio for the entire column.
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.
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?
Thanks in advance.
your help is much appreciated
Hi @WLFRD
It is similar. Create two measures.
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 |
Thanks for your help!
Much appreciated!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |