Forum Discussion

rohitbinnani's avatar
rohitbinnani
Icon for Advocate III rankAdvocate III
9 years ago

DAX query for Games Winning %

In power BI i want to filter by Team A's Bangalore and want Winning % of Bangalore as Team A. i.e. if i Filter with Bangalore from Team A i want (Winner/Team A)*100 which shows me the winning % of Ba...
Need Help
Show and Tell
Tutorial Requests
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi rohitbinnani,

 

Since column [Team A] belongs from a different table and [Winner] comes from another table, you should first combine them into a single table based on specific relationship. 

 

As I don't know what relationship is between the original two tables, suppose the combination table looks like above you posted. And there are two slicers in your report, one is for [Team A], another is for [Year]. In that case, please create measures like:

CountSelectedWinner =
CALCULATE (
    COUNTA ( 'Game Winning'[Winner] ),
    FILTER (
        'Game Winning',
        'Game Winning'[Winner] = LASTNONBLANK ( 'Game Winning'[Team A], 1 )
    )
)

CountTeamA =
CALCULATE (
 COUNTA ( 'Game Winning'[Team A] ),
 FILTER (
 'Game Winning',
 'Game Winning'[Team A] = LASTNONBLANK ( 'Game Winning'[Team A], 1 )
 )
)

Winning% = [CountSelectedWinner]/[CountTeamA]

 

If I have something misunderstood, please provide more description about your requirement.

 

Thanks,
Yuliana Gu

  • rohitbinnani's avatar
    rohitbinnani
    Icon for Advocate III rankAdvocate III
    9 years ago

    Hi v-yulgu-msft,

     

    i tried it with different table. what if my filter of Team A is in and connected in this table as per a primary key. we have team A in this table itself but i have another dimension from which i am bringing my Team A as filter just because that is also connected to some more tables to filter them too. can you help me in this ?

    the script you provided works perfect if they belong from same table. !!!

     

    Regards,

    Rohit Binnani.