Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Discretize Line Chart

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)]
 
 
I will appreciate any help you can provide!
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.
     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.