Discretize Line Chart

[Anonymous]

Hello there,

I have been trying to split the following line chart into several groups, according to the rangoDeTiempo column. The problem is that whatever I have done keeps mixing all the lines into one, which is the average of all the individual lines.

I am sure the problem is how I calculate/filter the measures used for the plot.
I've been looking for the solution in the forums and on the internet. I've tried several suggestions, but nothing helps.

For reference, I will add the links to the data that I have used and the .pbix file. I have changed some names in the data, and I will include just the columns needed to reproduce what I have done as it is sensitive data.

I have two tables:

• Table 1 Name: Frecuencia
  • Columns: horasDeUsoPromedio, n.Gente, n.Bajas, fecha
• Table 2 Name: Tiempo_de_Uso
  • Columns: horasDeUsopromedio, Rango.De.Tiempo

In Table 1, the fecha is in the form of "yyyy-mm-dd." Although the day is always 1, the date represents the whole (specific) month.

• horasDeUsoPromedio, is the average time of use.
• n.Gente is the number of people that used a product (which I don't include in the data) for a given average time (horasDeUsoPromedio). The most likely is that it will not change its value from 1 as the original dataset is highly dissected.
• n.Bajas are the number of people who used a product for a given average time (horasDeUsoPromedio) and suddenly stopped using it in a specific month.

Table 2 is used to assign a text label to the horasDeUsoPromedio column in Table 1. 

• horasDeUsoPromedio contains unique values of horasDeUsoPromedio from Table 1.
• Rango.De.Tiempo is a text label assigned to each horasDeUsoPromedio value.

The calculations I did are the following:
Measure 1:

YTD Average(Gente) =

CALCULATE(SUM('Frecuencia'[n.Gente]) /

(DATEDIFF(MIN('Frecuencia'[fecha]),

MAX('Frecuencia'[fecha]),

MONTH) + 1

),

FILTER(ALLSELECTED('Frecuencia'),

'Frecuencia'[fecha] > MAX('Frecuencia'[fecha]) - 365 &&

'Frecuencia'[fecha] <= MAX('Frecuencia'[fecha])

)

)

Measure 2:

YTD Bajas=

CALCULATE(

SUM('Frecuencia'[n.Bajas]),

FILTER(

ALLSELECTED('Frecuencia'),

'Frecuencia'[fecha] > MAX('Frecuencia'[fecha]) - 365 &&

'Frecuencia'[fecha] <= MAX('Frecuencia'[fecha])

)

)

Measure 3:

Frecuencia = [YTD Bajas]/[YTD Average(Gente)]

Drive URL: https://drive.google.com/drive/folders/1JxgQUa4sHCfZ2BlL9UX-krqEMTV0ugb2?usp=sharing

I will appreciate any help you can provide!

[Anonymous · Accepted solution]

Well, after several days of trying to fix this problem. I took a look at all the functions and thought that I could use the DISTINCT function as a second filter in the CALCULATE function of both, measure 1 and measure 2.

The result was what I expected. In case someone comes across a similar problem I modified the measures as follow:

Measure 1:

YTD Average(Gente) =

CALCULATE(SUM('Frecuencia'[n.Gente]) /

(DATEDIFF(MIN('Frecuencia'[fecha]),

MAX('Frecuencia'[fecha]),

MONTH) + 1

),

FILTER(ALLSELECTED('Frecuencia'),

'Frecuencia'[fecha] > MAX('Frecuencia'[fecha]) - 365 &&

'Frecuencia'[fecha] <= MAX('Frecuencia'[fecha])

),

DISTINCT(Frecuencia[horasDeUsoPromedio])

)

Measure 2:

YTD Bajas=

CALCULATE(

SUM('Frecuencia'[n.Bajas]),

FILTER(

ALLSELECTED('Frecuencia'),

'Frecuencia'[fecha] > MAX('Frecuencia'[fecha]) - 365 &&

'Frecuencia'[fecha] <= MAX('Frecuencia'[fecha])

),

DISTINCT(Frecuencia[horasDeUsoPromedio])

)

The next plot is the result:

In this case, I am selecting four values to segment the data.