cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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  
ModelTypevalue
A15000
A22020
A3589
B4532
B5577
B64890
B715423

 

table 2  
competitorTypeavailabilty
X1yes
X2yes
X3no
Y1yes
Y2no
Y3no
X4no
X5yes
X6no
X7yes
Y4yes
Y5yes
Y6yes
Y7yes

 

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

 

v-janeyg-msft_0-1602555838914.png

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.

View solution in original post

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

 

v-janeyg-msft_0-1602555838914.png

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.

View solution in original post

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
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!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors