Line and Stacked Column Chart

[Anonymous]

I am in a situation where I am needing to illustrate two different line values. One of the line values are much smaller numbers (very close to the values of my columns) and the other is a set of line values that are much larger. This is affecting my lines as I am seeing that i can only have the line values aligned to one side of the y axis or the other. 

Is there a way where I can keep both set of values in the line values section and have one set of line values aligned/positioned to the right side of y axis and the other on the left side along with my column value spread.

In the picture below, you can see my Target Value Line (orange) on the very bottom. That value is 1.04 and I am trying to have it related to the values in the left axis. 

[Greg_Deckler]

@Anonymous You could fake it 'till you make it. Perhaps you could multiply that value by a multiple like *75000 perhaps? Alternatively you could try for stacked visuals. One other thought, could you maybe add it to the Analytics pane?

[Anonymous]

@Greg_Deckler 

I tried using the analytics pane but only saw the trend line option which was not helpful for my situation.

I am now working on trying to figure out how to multiply the line value by another value such as 75,000. Am i able to do that through report view or will i need to make the calculation in query editor?

[Greg_Deckler]

@Anonymous You could create a measure like:

Measure = SUM('Table'[Column]) * 75000

You could use any aggregator function, COUNT, MAX, whatever not sure what you are doing or if you are already using a measure, multiply the end result there by 75000 or Measure = [Old Measure] * 75000

[Anonymous]

That worked! Now, as you can see, I am having trouble with figuring out how to apply the measure for all of the departments in my slicer as i will need to multiply a different value to make it fit (the first pic is the goal). Is there a DAX that i could use to filter to only specific department?

@Greg_Deckler 

[Greg_Deckler]

@Anonymous Perhaps something along the lines of:

Measure = SUMX(FILTER('Table',[Department]="Department1"),[Column]) * 75000

or

Measure = SUMX(FILTER(ALL('Table'),[Department]="Department1"),[Column]) * 75000

or even

Measure = SUMX(FILTER(ALLSELECTED('Table'),[Department]="Department1"),[Column]) * 75000

[Anonymous]

Awesome, that partially solved it... however i am having trouble with the different slicers keeping their specific Department's measure.

For example when i click on one department and have the related measure it works perfectly. And then when i click on a different department, it is keeping that same measure from the previous department which is essentially incorrect. I am having to replace the measure from the previous department to the one that i click on. 

I know this is a quick solve but not sure how it needs to be handled. Thank you so much for your help with this!

@Greg_Deckler 

[Greg_Deckler]

@Anonymous Try:

Measure = 
  VAR __Dept = MAX([Department])
RETURN
  SUMX(FILTER('Table',[Department]=__Dept),[Column]) * 75000

Depending on how your visuals are configured, may need ALL, ALLSELECTED thrown in there. 

[Anonymous]

@Greg_Deckler 

The first snip is correct as the target line is at the correct amount. 

The second snip target line is incorrect. However, they are both multiplied by the same amount (75,000).

How can i create a measure where i can use a different amount to multiply from in order for the target line value to be at the correct spot for each specific department.

Let me know if i need to attach any other screenshots of how my data is being shown.

[Anonymous]

Could you explain what you mean by adding it to the analytics pane?

@Greg_Deckler 

[Greg_Deckler]

@Anonymous 

[amitchandak]

@Anonymous , All the lines would always on the same axis in case online clustered visual, All left or all right.