Hey,
This one is a real challenge...
My orginal query provides Revenue by Customer by different timeframes - from date to date. Part of them monthly, part of them quarterly etc.
My aim is to have a view of Month over Month from 2 perpsectives:
1. By customer.
2. View for the whole organization.
Both using Bar Charts and Line.
The view by customer will show either positive or negative value per each month, as these are the values that a customer might have (he either increased or decreased MoM).
The org's view should show for each month both increased and decreased amounts (as some customers increased and some of them decreased). However, and very important, I want to show all the Decreased amounts together in the bar chart in red and mentioned as "Decreased" and all the increased amounts in yellow and highlighted as "Increased".
I had to first transform the details from different tieframes into daily perspective (in order to calculate the month Rev, I had to find the revenue by day).
In order to do that, what i did was creating 2 calendar reports - one to provide the current YearMonth of each date, and the other to provide the previous YearMonth of each date.
Then, i have created 2 new tables by crossjoin the original one with each of the calendar to have a view of current and previous month per each date.
Tehn I calculated the monthly amount in a measure (in both tables):
MonthlyAmount = CALCULATE(Sum(CurrentMonth[RevPerDay]),FILTER(ALLSELECTED('CurrentMonth'),'CurrentMonth'[Customer]=MAX('CurrentMonth'[Customer]) && 'CurrentMonth'[YearMonth]=MAX('CurrentMonth'[YearMonth])))
Then I calculated the MoM as the gap between the 2 measures.
I have added a measure to be Increase/Decrease in line with the MoM results.
I have managed to show the Bar Chart by customer (including a running total calculated as another measure).
My issue at the end of the day is:
When I want to provide a Bar Chart with the org's details, I cannot choose the measure of increase/decrease as the Column Series (legend) - it is just not available.
Moreover, If i don't use the Customer as the Column Series (legend) the values are incorrect (it only shows one customer data).
I have tried to provide all the details, to hear from you guys, whether you think the issue is somewhere along the way, and whether i should change something there.
Would be great to understand how to use a measure as Column Series in a bar chart in general, and specifically in the situation i have shared here.
Thanks a lot.
