cancel
Showing results for
Did you mean:
Regular Visitor

## Comparison between X & Y using many criteria

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

1 ACCEPTED SOLUTION Microsoft

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 )`````` 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.

3 REPLIES 3 Microsoft

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 )`````` 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.

Regular Visitor

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 Super User IV

@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

Tutorial Series Dax Vs SQL Direct Query PBI Tips  