How to calculate a dynamic percentage per month?

[Anonymous]

Hello everyone,

I am creating this thread because I can use some help with a DAX formula which needs to calculate dynamic percentages. I have tried a lot my self, but I can not seem to calculate percentages per month.

Here is some sample data: https://www43.zippyshare.com/v/K1bBT8up/file.html

Apart from this sample data I have created two calculated colums, which are:
Met SLA? = IF('Blad1'[End]<='Blad'[Should end before;"Yes";"No")

Amount "Yes"? = DIVIDE (
    CALCULATE ( COUNT ( 'Blad1'[Met SLA?] ); 'Blad1'[Met SLA?] = "Yes" );
    CALCULATE ( COUNT ( 'Blad1'[Met SLA?] ); ALLSELECTED ( 'Blad1'[Met SLA?] ) ); BLANK()
)

In a matrix I get the result below with my real data, but I want another column with the percentage of the 'amount yes/total amount' per month. Before drilling down I want it to show 'Company A' with the total percentage over the months.

Does anybody know how to realize this with a measure?

Thank you for your time and effort.

Best regards,

AliBI

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Do you want to add "company" and "date-month" in the "row" field of the matrix,  add "percentage" in the "value" field, so that you can drill down from "company" level to "company"&"date-month" level to see the correct percentage.

If so, create a column

yes/no = IF([End]<=[Should end before],"yes","no")

create measures 

count of yes = CALCULATE(COUNT(Table1[yes/no]),FILTER(Table1,Table1[Organisatie]=MAX(Table1[Organisatie])&&Table1[yes/no]="yes"))

total = CALCULATE(COUNT(Table1[yes/no]),FILTER(Table1,Table1[Organisatie]=MAX(Table1[Organisatie])))

percent = [count of yes]/[total]

You could download my pbix for more details.

Best Regards

Maggie

[Anonymous]

Hello @v-juanli-msft ,

Thank you!! This is exactly what I wanted!

Just wanted to ask you one question, since I want to learn from this: why did you add another calender/date table and set up a relation?

Thanks again, also for providing the .pbix file.

Best regards,

AliBI 

[v-juanli-msft]

Hi @Anonymous 

If "date" column in my "Table1" isn't continuous, it is better to use date column from another calendar date table in a matrix row field.

Best Regards

Maggie