Is it possible to calculate a percentage based on displayed values?

[AxlCox]

Simply put, my data set had years worth of data in rows. On my visuals, I've created simple cards to display count totals and filtering to only show rows created this year. Each of these tasks (the rows) have an SLA, which I display using another card, filtered by both created this year AND SLA Breached is TRUE. So it basically looks like this:

Total YTD

823

YTD SLA Breached

388

Rather than showing the breached as a total, I need it to show as a percentage of that 823 total. So it should really look like

Total YTD

823

YTD SLA Breached

47%

From my research, it seems like this all has to be calculated as a measure but I'm pretty green with DAX. If I could do a simple calculation using the actual pre-filtered visuals, that would rock, but as far as I can tell, that's not an option. Any insight would be appreciated! 

[AxlCox]

The actual data is highly confidential, but here's some dummy data that should be relevant to what I'm looking for.

Task No	Created Date	SLA Breached?
1	1/3/2019	No
2	2/4/2020	Yes
3	2/23/2021	Yes
4	5/4/2018	No
5	7/4/2018	No
6	12/22/2020	No
7	7/6/2021	No
8	11/22/2020	Yes
9	9/9/2020	Yes
10	10/11/2021	Yes

Using this data, I'd like to have two cards displayed like this (based on this data):

# Year To Date

3

% Year To Date Breached

67%

Right now, I'm using a filter on the data to show the # year to date. On the second card, I'm currently using a filter to only show those from this year AND SLA Breached = true.

To get the percentage of % Year To Date Breached, it's obviously simple division, but I am ignorant on how to show a calculation based on filtered data.

[smpa01]

@AxlCox  why 

# Year To Date = 3

There are 4 years in the data {2018,2018,2020,2021}. By # Year To Date  do you intend to show the total count of all distinct years?

Also, why 

% Year To Date Breached = 67%

what is the numerator and denominator and how are they calculated?

If the above is true it should be 50% = {2020="Yes" and 2021="Yes" out of 2018,2018,2020,2021}

[AxlCox]

Sorry - I didn't clarify. I am only targeting tasks created THIS year, so that's where the 3 comes from.

67% is coming from 2/3. (2 tasks SLA BREACHED is TRUE [Among tasks in 2021] / 3 tasks in 2021)

[smpa01]

@AxlCox  and what About  # Year To Date = 3

How are you getting that?

[AxlCox]

Year To Date may be the wrong identification stated. I'm just looking for thos created in 2021 (1/1/21 - today), not 11/3/20 - 11/3/21.

So just these:

Task No	Created Date	SLA Breached?
1	1/3/2019	No
2	2/4/2020	Yes
3	2/23/2021	Yes
4	5/4/2018	No
5	7/4/2018	No
6	12/22/2020	No
7	7/6/2021	No
8	11/22/2020	Yes
9	9/9/2020	Yes
10	10/11/2021	Yes

[smpa01]

@AxlCox  

M1 =
VAR _0 =
    ADDCOLUMNS ( 'Table', "@year", YEAR ( [Created Date] ) )
VAR _1 =
    FILTER ( _0, [@year] = MAXX ( _0, [@year] ) )
RETURN
    COUNTROWS ( _1 )


M2 =
VAR _0 =
    ADDCOLUMNS ( 'Table', "@year", YEAR ( [Created Date] ) )
VAR _1 =
    FILTER ( _0, [@year] = MAXX ( _0, [@year] ) )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "@ratio",
            DIVIDE (
                COUNTX ( FILTER ( _1, [SLA Breached?] = "Yes" ), [SLA Breached?] ),
                COUNTX ( _1, [SLA Breached?] )
            )
    )
RETURN
    MAXX ( _2, [@ratio] )

 Pbix is attached

[AxlCox]

Making some progress here....

M1 is working (the column names in the actual data differ, but I got it figured out). 

However M2, isn't working but I take full fault as I provided the wrong criteria. Using the same data structure as an example, but modified...:

Task No	Created Date	Duration (days)
1	1/3/2019	5
2	2/4/2020	11
3	2/23/2021	45
4	5/4/2018	17
5	7/4/2018	19
6	12/22/2020	25
7	7/6/2021	8
8	11/22/2020	28
9	9/9/2020	25
10	10/11/2021	56

The percentage I need to show is the Percent of these rows that are 1) Created in 2021 and 2) Have duration > 30. 

In this example, it would still be 67%.

2 <- 2 rows created in 2021 AND >30 days duration

/

3 <- 3 rows created in 2021

I tried taking a crack at modifying the DAX as I thought it would be, but no dice.

[smpa01 · Accepted solution]

@AxlCox  try this

M3 =
VAR _0 =
    ADDCOLUMNS ( t2, "@year", YEAR ( [Created Date] ) )
VAR _1 =
    FILTER ( _0, [@year] = MAXX ( _0, [@year] ) )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "@ratio",
            DIVIDE (
                COUNTX ( FILTER ( _1, [Duration] > 30 ), [Duration] ),
                COUNTX ( _1, [Duration] )
            )
    )
RETURN
    MAXX ( _2, [@ratio] )

[AxlCox]

Hrm, yeah that's actually what I entered. I'm getting a value, but it's coming back at 1%.  It should be 52.6%.

Here's my actual code, with the true column names that I subbed in:

% >30 Days = 
VAR _0 =
    ADDCOLUMNS ( 'RFPs', "Calc Year", YEAR ( [Created] ) )
VAR _1 =
    FILTER ( _0, [Calc Year] = MAXX ( _0, [Calc Year] ) )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "@ratio",
            DIVIDE (
                COUNTX ( FILTER ( _1, [Duration of RFP] > 30 ), [Duration of RFP] ),
                COUNTX ( _1, [Duration of RFP] )
            )
    )
RETURN
    MAXX ( _2, [@ratio] )

The M1 formula returned 823, which is the correct count for 2021 entries, matching my original filtered value.

M2 should be coming back at 52.6%, based off of 433 rows having both 1) Been created in 2021 and 2) Having a duration >30. 

Here are the visual filters producing that 433 number:

[smpa01]

@AxlCox  I am acting on the sample data and the measure is absolutely working..see attached

[AxlCox]

Follow up question to this query:

% >30 Days = 
VAR _0 =
    ADDCOLUMNS ( 'RFPs', "Calc Year", YEAR ( [Created] ) )
VAR _1 =
    FILTER ( _0, [Calc Year] = MAXX ( _0, [Calc Year] ) )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "@ratio",
            DIVIDE (
                COUNTX ( FILTER ( _1, [Duration of RFP] > 30 ), [Duration of RFP] ),
                COUNTX ( _1, [Duration of RFP] )
            )
    )
RETURN
    MAXX ( _2, [@ratio] )

Is this always going to abide by the latest calendar year or the latest calendar year found in a conditional data set? 

In other words, if I add a slicer to select years/months, if I pick 2020  rather than 2021, will this query then see the filtered data ONLY, and therefore the MAXX year is now 2020 or is it still looking at the full data table which contains 2021 and therefore that's the year it's abiding by?

[AxlCox]

I figured it out. After all that, the formatting wasn't set to percentage. 

Thanks for your help!

[Greg_Deckler]

@AxlCox Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.