cancel
Showing results for
Search instead 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

Accepted Solutions
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 )``````

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.

3 REPLIES 3
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

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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.

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

Helpful resources

Announcements

Check it Out!

Click here to read more about the November 2020 Updates!

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors