Calculating Percentage of Total of a subset

[Anonymous]

Hi guys!

I've searched around and am unable to replicate a solution that works with my problem, so I'm hoping to find some help here!

I'm trying to calculate a percent of total while also excluding some values. For example, in the table below I want to calculate the total of % Won and also the total of % Lost, while excluding the numbers for any that are Open, Abandoned, or Alternates. 

So the end result that I'm driving towards would use a total amount of 43 units (sum of units - excluding open, abandoned, and alternates) with a Won % of 44% and a Lost % of 56%. 

My end goal is to be able to display these two percentages in seperate cards on my report that would also adjust when a single region or zone is selected. 

Zone	Region	Status	Units
1	A	Lost	1
1	A	Open	5
1	B	Won	6
1	C	Abandoned	4
1	C	Alternate	1
1	C	Won	5
2	D	Won	6
2	D	Lost	9
2	E	Lost	2
2	F	Lost	4
2	F	Abandoned	7
2	F	Open	3
2	F	Won	2
2	G	Lost	8

[TomMartens · Accepted solution]

Hey @Anonymous 

I created two measures, one that calculates the percentages for the status and the other one for the status lost

Measure Lost = 
var just_Won_Lost = 
    CALCULATE( 
        SUM( 'Table'[Units] )
        , KEEPFILTERS(
            'Table'[Status] IN { "Won" , "Lost" }
        )
    )
var just_lost = 
    CALCULATE( 
        SUM( 'Table'[Units] )
        , KEEPFILTERS(
            'Table'[Status] IN { "Lost" }
        )
    )
return
DIVIDE( just_lost , just_Won_Lost)

and the other one

Measure WON = 
var just_Won_Lost = 
    CALCULATE( 
        SUM( 'Table'[Units] )
        , KEEPFILTERS(
            'Table'[Status] IN { "Won" , "Lost" }
        )
    )
var just_Won = 
    CALCULATE( 
        SUM( 'Table'[Units] )
        , KEEPFILTERS(
            'Table'[Status] IN { "Won" }
        )
    )
return
DIVIDE( just_Won , just_Won_Lost)

And here is a screenshot using the measures inside a table visual and on two card visuals:

Hopefully, this is what you are looking for.

Regards,

Tom

[Anonymous]

@TomMartens 

This worked perfectly! Thank you for your help!

[negi007]

@Anonymous 

Create 3 measures

1. Total_Win_Loss = CALCULATE(SUM(Win_Lost[Units]),Win_Lost[Status] in {"Lost", "Won"})

2. Win% = DIVIDE(CALCULATE(SUM(Win_Lost[Units]),Win_Lost[Status]="Won"),[Total_Win_Loss])

3. Loss% = DIVIDE(CALCULATE(SUM(Win_Lost[Units]),Win_Lost[Status]="Lost"),[Total_Win_Loss])

[Anonymous]

@negi007 

I tried creating your measures - no problem with the first one. On the second one, I'm getting the following error "Too few arguments were passed to the DIVIDE function. The minimum argument count for the function is 2."

I pasted below my current formula. Am I missing something?

Win % = DIVIDE(CALCULATE(SUM(opportunities[dms_countasunit]),opportunities[Status]="Won",opportunities[Total Win Loss])

[negi007]

@Anonymous 

have modified dax below, it should be working for you

Total_Win_Loss = CALCULATE(SUM(Win_Lost[Units]),Win_Lost[Status] in {"Lost", "Won"})

Win% = DIVIDE(CALCULATE(SUM(Win_Lost[Units]),Win_Lost[Status]="Won"),[Total_Win_Loss],0)

Loss% = DIVIDE(CALCULATE(SUM(Win_Lost[Units]),Win_Lost[Status]="Lost"),[Total_Win_Loss],0)