Getting Percentage per Month instead of per Year

[Anonymous]

Hello!

I need help on how to display the actual percentage of a specific value per month. What I mean is, I have this column called SLA where it has only two values: either Yes or No.

In this example, for January-2024, there are 384 rows where the value for SLA is Yes, then 371 rows of Yes for February-2024.

As an additional info, let's say March has 500 rows, but 250 of them is Yes and the other 250 is No. In this case, March-2024 should show 50%.

What's expected is that these column in the bar-line graph should show as 100% each, but currently I can only choose an option where the percentage shown is for the whole year. 

Your help is greatly appreciated. Thank you!

[Anonymous · Accepted solution]

Hi everyone, thank you to all who answered! I was able to make it work and here is the method I used:

1. Create a DAX formula to get the total rows of the whole table:

TotalRows = 

COUNTROWS('TableName')

2. Create another formula to get all the rows with Yes as a value in the SLA column

TotalYesCount =

CALCULATE(

    COUNTROWS('TableName'),

        (

        'TableName'[ColumnName] = "Yes"))

3. Create a formula that uses a DIVIDE function for the first two measures

PercentageOfYes =

DIVIDE(

    [TotalYesCount],

    [TotalRows],

    0

) * 1

I used * 1 because when I used *100, the percentage shown in the chart is over 9000

And that's how I was able to get this chart percentage per month.

[v-aatheeque]

Hi @Anonymous ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[Bibiano_Geraldo]

hI @Anonymous ,
Please adapt the following measure to attend your needs:

% of TotalEachMonth = 
-- COUNT ALL ROWS IN THE TABLE
VAR total = CALCULATE(
    COUNTROWS(financials) 
)


-- COUNT ALL ROWS MARKED  AS YES
VAR totalPercent = 
    CALCULATE(
        COUNTROWS(financials),
        FILTER(
            financials,
            financials[Custom] = "yes"
        )
    )

RETURN

-- GET PERCENTAGE
DIVIDE(
    totalPercent,
    total
)

As you can see bellow, the % is based in each month context and not in whole year, example jan has 100 rows, but 20 are maarked as yes, so the output for jan will be 20%:

Don't forget to replace columns and table names with your owns.

[danextian]

Hi @Anonymous 

I am confused. Why would you want to show 100% for all the columns? Unless you actually add a measure to the column tile a constant 100%, they wont be showing 100%.

And what do you mean by "but currently I can only choose an option where the percentage shown is for the whole year "?

[Anonymous]

Hi Dan! I don't need to show all columns as 100%. Here's an example of what I mean:

1. Let's say January has a total of 100 items/rows, and out of that 100, 50 says 'No' and the other says 'Yes'. In this case, it should show 50% as the percentage.

2. But if January has 100 'Yes' out of 100, then it should show 100% as the percentage.

As an additional info, this chart should show only those that are 'Yes'.


As for your other question, kindly see the attached picture. I mentioned that last part of my original post because in Excel, there's an option to show Percentage from Parent Column or something.

[danextian]

You need to start creating explicit measures and not rely on that. That is basically giving you the percentage across all months for all months visible.  Sample measure:

DIVIDE (
    CALCULATE ( COUNTROWS ( 'table' ), 'table'[SLA] = "Yes" ),
    COUNTROWS ( 'table' )
)