cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MoKi Frequent Visitor
Frequent Visitor

Measure total amount per month / total amount per location / total amount per sales type

Hello. I would be very happy about your help.

I have one big table, including columns: locations, sales type, amount is, amount target, date (from 1.1.18 until 1.4.19).

 

Now I would like to have just the sum of the amount for each location and also the sum of the amount for each sales type. And then I would like to compare the locations among themselves and the sales types among themselves. Do I have to create a new table for each location? And if yes, how?

 

And also: In my big table I would like to have the sum of my amounts for one month. For example the sum of my amounts for april and then compare it with the sum of the previous amount. Do I have to use a measure? I tried different formulas, but it didn´t work.

 

Thank you a lot for help.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Measure total amount per month / total amount per location / total amount per sales type

@MoKi ,

 

You may try Matrix visual and take a look at this post.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MoKi Frequent Visitor
Frequent Visitor

Re: Measure total amount per month / total amount per location / total amount per sales type

Tank you for your answer. This doesn´t help me. I triede using the following steps. And I will mention my problems: 

For the item:

" In my big table I would like to have the sum of my amounts for one month. For example the sum of my amounts for april and then compare it with the sum of the previous amount. Do I have to use a measure? I tried different formulas, but it didn´t work."

I created the measure: 

previous_month = CALCULATE(SUM(Table1[amount]);DATEADD(Table1[Date];-1;MONTH))
Using a matrix, the rows for the previous month are empty, but for every location I get a sum for previous month. Why are the rows empty?

 

Then I tried to use the following:
current_month_sales = CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=MONTH(TODAY())))

last_month_sales = var current_month= MONTH(TODAY()) return  CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=current_month -1))

 

But the problem here: Power BI sums the same month of each year, e.g. amount of April 2018 and 2019.

 

And can I create a measure, where do I get just the amount per location and a second measure to get the amount from the sales type?

 

If I want to compare two locations or two sales types, are measures the best opportunity?


Thanks a lot.