Forum Discussion

jjustjjo's avatar
jjustjjo
Icon for Helper II rankHelper II
7 days ago
Solved

Set Aggregation on a line chart

Should be as easy as right-clicking and choosing sum, min, max, etc, right?  Nope.

I calculate the days between 2 dates which are specified by slicer ( DATEDIFF([Comp StudyToSt Days Meas1],[Comp Site Days Meas2], DAY) ).  When I check a table of values, it is correct.

Line chart comes out like this: 

I've no idea where those numbers are coming from or how they are calculated.  when I try to change thew aggregation, I get this: 

.

Need Help
power bi
  • maruthisp's avatar
    maruthisp
    7 days ago

    Hi jjustjjo​,

    Step 1 – Decide what aggregation you actually need

For example, if you want the average number of days for all studies/sites shown at each point on the X-axis, you need to explicitly create that aggregation in DAX.

Assuming STUDY_SEQ identifies the individual records/studies, you can create:

Average Days Difference =
AVERAGEX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

This is a measure.

AVERAGEX first evaluates [Days Difference] for each STUDY_SEQ, and then calculates the average of those results.

Step 2 – If you need Minimum

Minimum Days Difference =
MINX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

Step 3 – If you need Maximum

Maximum Days Difference =
MAXX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

Step 4 – If you need Sum

Total Days Difference =
SUMX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

Step 5 – Use the required measure in the line chart

For example:

X-axis:
Date / Year / Study Level / required category

Y-axis:
Average Days Difference

    Best Regards,
    Maruthi
    LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
    X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 days ago

    Hi,

    Which 2 dates are you trying to find the difference of?  Are any filters active for this visual?  If nothing helps, then please share the download link of the PBI file.

  • maruthisp's avatar
    maruthisp
    Icon for Super User rankSuper User
    7 days ago

    Hi jjustjjo​,

    Step 1 – Decide what aggregation you actually need

For example, if you want the average number of days for all studies/sites shown at each point on the X-axis, you need to explicitly create that aggregation in DAX.

Assuming STUDY_SEQ identifies the individual records/studies, you can create:

Average Days Difference =
AVERAGEX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

This is a measure.

AVERAGEX first evaluates [Days Difference] for each STUDY_SEQ, and then calculates the average of those results.

Step 2 – If you need Minimum

Minimum Days Difference =
MINX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

Step 3 – If you need Maximum

Maximum Days Difference =
MAXX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

Step 4 – If you need Sum

Total Days Difference =
SUMX (
    VALUES ( 'Milestones Info'[STUDY_SEQ] ),
    [Days Difference]
)

Step 5 – Use the required measure in the line chart

For example:

X-axis:
Date / Year / Study Level / required category

Y-axis:
Average Days Difference

    Best Regards,
    Maruthi
    LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
    X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    6 days ago

    Hi jjustjjo​ 

    Your “Comp Study-Site Days” is a measure, not a column, so Power BI won’t offer Sum/Min/Max. A measure’s aggregation is defined inside its DAX. That’s why you only see “Show value as”.

    The odd numbers come from filter context: on the line chart, each point evaluates [Comp StudyToSt Days Meas1] and [Comp Site Days Meas2] within that year only, so they return different dates than in your table. The negative values mean the first date is later than the second in that context. Drop Year, Meas1 and Meas2 into a table to see exactly what each returns per year.

    To control the aggregation, wrap the calculation in an iterator at the right grain, e.g.:

    Avg Study-Site Days = AVERAGEX( VALUES('Table'[SiteID]), DATEDIFF([Comp StudyToSt Days Meas1], [Comp Site Days Meas2], DAY) )

    Use SUMX/MINX/MAXX as needed. Also, if your slicer and the chart’s axis use the same date table, they filter each other. Consider a disconnected date table for the slicer, or REMOVEFILTERS on the date table inside the measures.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.