Forum Discussion
Line Chart to represent individual data as well as sum
Microsoft EmployeeHi RealEstateDude,
To just show the Metro data when choosing "ALL" Submarkets, you should be able to use ISFILTERED Function (DAX) to in your formula to create the measure. The formula below is for your reference. :smileyhappy:
=
IF (
ISFILTERED ( 'Submarket'[Submarket] ),
CALCULATE (
SUM ( 'Submarket'[Vacant Units] ) / SUM ( 'Submarket'[Units)] ),
ALLEXCEPT (
'Submarket',
'Submarket'[Submarket],
DateDim[Year],
'Submarket'[Quarter Digit],
Metro[Metro]
)
)
)
Regards
Hi v-ljerr-msft
Thanks! This solution works exactly like I said I needed it to! However, I'm now realizing I need something a little bit different. The set of data I'm working with looks like this, with all relevant Submarket data contained in one spreadsheet:
On my vizualization, I need to show the Year End(Y) Submarket Vac % figure for all years except the current, where I need to show the previous quarter (in this case 2017 Q3). I've highlighted the relevant rows in yellow to help you see what I'm talking about. The Vac % is found by dividing the Units by the Vacant Units (939/15,124 = 6.2), or more simply, just finding the Vac % for that time period (Q3 2017 = 6.2). The sum of all individual submarkets for that time period is equal to the Metro data for that same period (ex. Metro = 151,240 Units, 10 Submarkets * 15,124 = 151,240). So, 939 Vacant Units * 10 Submarkets = 9,390 / 15,124 Units * 10 Submarkets = 151,240 - 9,390/151,240 = 6.2. With a different (much simpler) set of data I was able to accomplish what I wanted:
The indiviudal Submarket data is shown when selected, and the Metro data (which is the same as a sum of all Submarkets) is shown when ALL is selected - technically, they are both being shown, but the Submarket data is laid over the Metro for presentation purposes (it get messy when both lines show data labels):
While your previous formula is exactly what I said I needed, eliminating the Submarket data requires the data labels to be turned on for the Meto. Otherwise, the output is a line that doesn't really mean much:
Hopefully, I've explained well enough what I'm trying to produce. Please let me know if any clarification is needed. Thanks!