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 beginning with PowerBI and I'm currently stuck , I know how to get what I want to see in Excel, but not in PowerBI so I'm requesting some help
Hope example is clear enough. Thank you in advance
table 1 | ||
Model | Type | value |
A | 1 | 5000 |
A | 2 | 2020 |
A | 3 | 589 |
B | 4 | 532 |
B | 5 | 577 |
B | 6 | 4890 |
B | 7 | 15423 |
table 2 | ||
competitor | Type | availabilty |
X | 1 | yes |
X | 2 | yes |
X | 3 | no |
Y | 1 | yes |
Y | 2 | no |
Y | 3 | no |
X | 4 | no |
X | 5 | yes |
X | 6 | no |
X | 7 | yes |
Y | 4 | yes |
Y | 5 | yes |
Y | 6 | yes |
Y | 7 | yes |
what I'm looking for is :
what Y is missing vs X only when X is available
So 100% is what is available from X only ,then I want to see what we miss with Y vs X
Solved! Go to Solution.
Hi, @cocolinho
It’s my pleasure to answer for you.
I’m a little confused about what you want to calculate, but I tried to understand and calculated the percentage of the total when X=YES and Y=NO under the same type.
Like this:
Measure =
VAR tab =
SUMMARIZE (
ALL ( TableXY ),
TableXY[type],
"y=no",
VAR x =
COUNTROWS (
FILTER (
ALL ( TableXY ),
TableXY[type] = EARLIER ( TableXY[type] )
&& TableXY[availbility] = "YES"
&& TableXY[competitor] = "X"
)
)
VAR y =
COUNTROWS (
FILTER (
ALL ( TableXY ),
TableXY[type] = EARLIER ( TableXY[type] )
&& TableXY[availbility] = "NO"
&& TableXY[competitor] = "Y"
)
)
RETURN
IF ( x > 0 && y > 0, 1, 0 ),
"x=yes",
VAR x =
COUNTROWS (
FILTER (
ALL ( TableXY ),
TableXY[type] = EARLIER ( TableXY[type] )
&& TableXY[availbility] = "YES"
&& TableXY[competitor] = "X"
)
)
RETURN
IF ( x > 0, 1, 0 )
)
VAR yno =
COUNTROWS ( FILTER ( tab, [y=no] = 1 ) )
VAR xyes =
COUNTROWS ( FILTER ( tab, [x=yes] = 1 ) )
RETURN
DIVIDE ( yno, xyes )
If it doesn’t solve your problems, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @cocolinho
It’s my pleasure to answer for you.
I’m a little confused about what you want to calculate, but I tried to understand and calculated the percentage of the total when X=YES and Y=NO under the same type.
Like this:
Measure =
VAR tab =
SUMMARIZE (
ALL ( TableXY ),
TableXY[type],
"y=no",
VAR x =
COUNTROWS (
FILTER (
ALL ( TableXY ),
TableXY[type] = EARLIER ( TableXY[type] )
&& TableXY[availbility] = "YES"
&& TableXY[competitor] = "X"
)
)
VAR y =
COUNTROWS (
FILTER (
ALL ( TableXY ),
TableXY[type] = EARLIER ( TableXY[type] )
&& TableXY[availbility] = "NO"
&& TableXY[competitor] = "Y"
)
)
RETURN
IF ( x > 0 && y > 0, 1, 0 ),
"x=yes",
VAR x =
COUNTROWS (
FILTER (
ALL ( TableXY ),
TableXY[type] = EARLIER ( TableXY[type] )
&& TableXY[availbility] = "YES"
&& TableXY[competitor] = "X"
)
)
RETURN
IF ( x > 0, 1, 0 )
)
VAR yno =
COUNTROWS ( FILTER ( tab, [y=no] = 1 ) )
VAR xyes =
COUNTROWS ( FILTER ( tab, [x=yes] = 1 ) )
RETURN
DIVIDE ( yno, xyes )
If it doesn’t solve your problems, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks already @v-janeyg-msft & @amitchandak for your replies. I didn't have time to go through it yet but I will very soon and keep this thread up to date.
Cheers
@cocolinho , based on what I got. You can get table like
except(Summarize(filter(Table, Table[availabilty]="yes" , Table[competitor] ="X"), Table[availabilty]),
Summarize(filter(Table, Table[availabilty]="yes" , Table[competitor] ="Y"), Table[availabilty]))
You can use it var table in a measure
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |