topic How to get Totals per month in DAX Commands and Tips

How to get Totals per month

sconconi — Fri, 20 Sep 2019 15:33:13 GMT

Hi all!

I have the table "C&R BI" with the total of invoices done in each month and I want to add a column to obtain the percentage of Credit & Rebills done per month by my team, which I have in another table. So, basically, I need to divide the total of C&R done per Total of invoices. Problem is that I don't have the total per month of C&R done.

In table "C&R BI" I have the total invoiced per month, with the following columns, as an example:

Month/Year | Total invoiced

January 2018 | 300000

In another table, named "C&R Report", I have a list for each C&R done with the month in each line. It would be as follow:

Month/Year | Count

January 2018 | 1*

*The "1" represents that it's a C&R, which will help with the SUM.

I have also a Date dimension table.

How can I add a column to do that calculation? Get the total of C&R per month and divide it per Total invoiced.

My Excel mind thought in a SUMIF, but I found that it's not available in PowerBI (or I don't know how it's the code).

Thanks for your help!

Re: How to get Totals per month

Nathaniel_C — Sat, 21 Sep 2019 00:07:41 GMT

sconconi ,

Can you show us your model? Are these two tables linked? To which table are you adding the column?

Thanks,

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: How to get Totals per month

sconconi — Thu, 26 Sep 2019 12:30:53 GMT

Hi, thanks for your reply and sorry for the delay, I was very busy at work :(

I was able to solve it with the below formula that I'd found on another site:

CALCULATE(SUM('Table1'[Sheet1]),ALLEXCEPT('TAble2','TAble2'[Date]))

It didn't work out before because I didn't have the tables linked, once I did that, the formula worked.

Now I have another problem, since my supervisor asked me to modify it :smileysad:

Should I explain it here or should I open another topic?

Thanks in advance!

Re: How to get Totals per month

Nathaniel_C — Thu, 26 Sep 2019 13:49:47 GMT

Hi sconconi , I would start a new post. Nathaniel