Forum Discussion
Condition Column not giving the right values
Hello All,
I have revenue table which has a coImn for projected revenue and actual revenue,I'm trying to comeup with a line chart where I have only one line which would be solid till the current month ( Actual revenue) and dotted for the rest of the year( Projected).
For example : This would be for the Apr-Mar FY, April being my first monthI will have the actual revenue and it would be 0 for the remaining months. So my graph should show the April month actual revenue and for the remaining month it shoud show dotted line with projected revenue values.
I'm trying to do it as follows, any other suggestion will be helpful
- I 'm creating a condition column , which I will use to plot the graph
IF Monthly Revenue = 0 THEN Projected Revenue
ELSE Monthly Revenue
The new column is giving me the right value for the future months, but for the current month I'm getting a different value
In the table below "Revenue & Projection" column value for April should have been equal to "Monthly Revenue" value
| Month | Monthly Revenue | Proj. Rev | Revenue & Projection |
| Apr | 6640173.415 | 7754520 | 7205289.415 |
| May | 0 | 7251804 | 7251804 |
| Jun | 0 | 7388556 | 7388556 |
| Jul | 0 | 7352220 | 7352220 |
| Aug | 0 | 6580656 | 6580656 |
| Sep | 0 | 6782004 | 6782004 |
| Oct | 0 | 6735390 | 6735390 |
| Nov | 0 | 6359340 | 6359340 |
| Dec | 0 | 6944298 | 6944298 |
| Jan | 0 | 6191766 | 6191766 |
| Feb | 0 | 5889000 | 5889000 |
| Mar | 0 | 6757998 | 6757998 |
- To do something like this you have to have 2 lines in a chart. One will be the real revenue and the other will be the projected one. Then you'll be able to set the attributes of each line separately. With one line and one measure it's not achievable.
So, create 2 measures. One with the actual revenue and the other with a projection and put in one line chart. Customize both lines.
Best
D
4 Replies
- deevaker
Resolver I
Hi Anand, Can you please share your dataset and DAX formula that you have written.
Hi Anand,
Your approach to writing Dax was correct. I tried it by myself and found it working.
Result:
Result_Table
Dax:
combined =
IF (
'Table'[Monthly Revenue] = 0,
'Table'[Proj. Rev],
'Table'[Monthly Revenue]
)Final Visual:
trend_visual
Regards,
Kaushlendra Mishra
Linkedin-www.linkedin.com/in/kaushlendra-mishra-49627310
- To do something like this you have to have 2 lines in a chart. One will be the real revenue and the other will be the projected one. Then you'll be able to set the attributes of each line separately. With one line and one measure it's not achievable.
So, create 2 measures. One with the actual revenue and the other with a projection and put in one line chart. Customize both lines.
Best
D
