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.
Hi everyone!
I'm having some troubles while calculating some values.
I was doing it in excel with dynamic tables, but now I want to make a step forward and start using Power BI 🙂
I have this table
Month | Year | Cond1 | Cond2 | Cond3 |
1 | 2017 | 0 | Open | A |
1 | 2017 | 0 | Open | A |
2 | 2017 | 0 | Close | A |
2 | 2017 | 1 | Open | A |
3 | 2017 | 1 | Close | B |
3 | 2017 | 0 | Open | B |
What I need is the following:
I need show per Month & Year this division:
Count(Cond1 = 0 && Cond2 = Open && Cond3 = A) / Count (Cond1 = 0 && Cond2 = Open)
Is it possible?
Kind regards!
Solved! Go to Solution.
Hi, you can try to obtain your result with this:
Count to Cond1 is 0 and Cond2 Open and Cond3 is A = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" && Table1[Cond3] = "A" ) )
Count to Cond1 is 0 and Cond2 Open = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" ) )
Result = DIVIDE ( [Count to Cond1 is 0 and Cond2 Open and Cond3 is A], [Count to Cond1 is 0 and Cond2 Open], BLANK () )
With 3 measure or All together in a single Measure
Measure = VAR Count_to_Cond1_is_0_and_Cond2_Open_and_Cond3_is_A = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" && Table1[Cond3] = "A" ) ) VAR Count_to_Cond1_is_0_and_Cond2_Open = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" ) ) RETURN DIVIDE ( Count_to_Cond1_is_0_and_Cond2_Open_and_Cond3_is_A, Count_to_Cond1_is_0_and_Cond2_Open, BLANK () )
Regards
Victor
Lima - Peru
Hi, you can try to obtain your result with this:
Count to Cond1 is 0 and Cond2 Open and Cond3 is A = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" && Table1[Cond3] = "A" ) )
Count to Cond1 is 0 and Cond2 Open = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" ) )
Result = DIVIDE ( [Count to Cond1 is 0 and Cond2 Open and Cond3 is A], [Count to Cond1 is 0 and Cond2 Open], BLANK () )
With 3 measure or All together in a single Measure
Measure = VAR Count_to_Cond1_is_0_and_Cond2_Open_and_Cond3_is_A = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" && Table1[Cond3] = "A" ) ) VAR Count_to_Cond1_is_0_and_Cond2_Open = COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" ) ) RETURN DIVIDE ( Count_to_Cond1_is_0_and_Cond2_Open_and_Cond3_is_A, Count_to_Cond1_is_0_and_Cond2_Open, BLANK () )
Regards
Victor
Lima - Peru
Hi @Vvelarde It worked with the 3 mesasures separated. With the other the result it made no sense.
Thanks!!!
Kind regards
Hi @chrisgehm
Try this...
Meaures:
0, Open and A = IF(ISBLANK(CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open";Table1[Cond3] = "A"));0;CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open";Table1[Cond3] = "A"))
0 and Open = IF(ISBLANK(CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open"));0;CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open"))
Division = IF(ISBLANK(DIVIDE([0, Open and A];[0 and Open]));0;DIVIDE([0, Open and A];[0 and Open]))
Regards
BILASolution
Hi @BILASolution!
Thanks for the answer.
It seems to be working, but at some piont I can notice an error:
This is my data set
So, with the first 2 measures, I've got 67 and 34
But when I make the division, it makes no sense, since the result is 79.83. It should be 0.50
Is there any mistake I'm making?
Kind regards
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |