Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AntoineC
Frequent Visitor

Trend line in chart with custom x-axis not working properly

Hi everyone,

 

I have a dashboard with a Combo Chart that displays agregated calculations for the previous 13 months. I added a trend line in DAX that works pretty well in the monthly chart.

Monthly.png

 

I also have the same dashboard for the previous 13 weeks. The weekly chart is slightly different from the monthly chart because the x-axis is not a date but a custom string done in DAX with the following format : "Week number (first date of week)"

Weekly.png

 

My DAX trend mesure doesn't work for my weekly chart and outputs the following error : "Calculation error in measure [Weekly Trend]: The function SUMX cannot work with values of type String"

 

The weekly trend mesure is the one below, the y-axis values in the chart are from the measure [AGREGATED VALUE], the x-axis being 'T_DATES_GRAPH'[Weekly Axis] :

 

Weekly Trend = 
VAR Known = FILTER(
        SELECTCOLUMNS(ALLSELECTED('T_DATES_GRAPH'[Weekly Axis]),"Known[X]", 'T_DATES_GRAPH'[Weekly Axis],"Known[Y]", [AGREGATED VALUE]),
        AND(NOT(ISBLANK(Known[X])), NOT(ISBLANK(Known[Y])))
    )
VAR Count_Items = COUNTROWS(Known)
VAR Sum_X = SUMX(Known, Known[X])
VAR Sum_X2 = SUMX(Known, Known[X]^2)
VAR Sum_Y = SUMX(Known, Known[Y])
VAR Sum_XY = SUMX(Known, Known[X] * Known[Y])
VAR Average_X = AVERAGEX(Known, Known[X])
VAR Average_Y = AVERAGEX(Known, Known[Y])
VAR Slope = DIVIDE(Count_Items * Sum_XY - Sum_X * Sum_Y,Count_Items * Sum_X2 - Sum_X^2)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
AVERAGEX(DISTINCT('T_DATES_GRAPH'[Weekly Axis]), Intercept + Slope * 'T_DATES_GRAPH'[Weekly Axis])

 

 

The [Weekly Axis] is a calcuted column with the following measure, DATEP being the last date of the week

 

Weekly Axis = "W" & [WeekNumber] & " (" & FORMAT([DATEP],"dd/mm") & ")"

 

 

The problem probably resides in the fact that for my Weekly Trend measure DAX can't properly order an x-axis with string values, but it works with date values.

I can't get my head around this... If someone knows how to solve or bypass such a problem it would greatly help me!

1 ACCEPTED SOLUTION
AntoineC
Frequent Visitor

For anyone interested I managed to solve it. The trick was to get the measure to work with a date range while leaving the x-axis of the chart with my custom measure (Week number + first day of week). I had to include both those fields in the SELECTCOLUMNS and it works like a charm. See the corrected measure below:

Weekly Trend = 
VAR Known = FILTER(
        SELECTCOLUMNS(ALLSELECTED('T_DATES_GRAPH'[DATEP],'T_DATES_GRAPH'[Weekly X Axis]),"Known[X1]",'T_DATES_GRAPH'[DATEP],"Known[X2]",'T_DATES_GRAPH'[Weekly X Axis],"Known[Y]",[AGREGATED VALUE]),
        AND(NOT(ISBLANK(Known[X1])), NOT(ISBLANK(Known[Y])))
    )
VAR Count_Items = COUNTROWS(Known)
VAR Sum_X = SUMX(Known, Known[X1])
VAR Sum_X2 = SUMX(Known, Known[X1]^2)
VAR Sum_Y = SUMX(Known, Known[Y])
VAR Sum_XY = SUMX(Known, Known[X1] * Known[Y])
VAR Average_X = AVERAGEX(Known, Known[X1])
VAR Average_Y = AVERAGEX(Known, Known[Y])
VAR Slope = DIVIDE(Count_Items * Sum_XY - Sum_X * Sum_Y,Count_Items * Sum_X2 - Sum_X^2)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
AVERAGEX(DISTINCT('T_DATES_GRAPH'[DATEP]), Intercept + Slope * 'T_DATES_GRAPH'[DATEP])

View solution in original post

3 REPLIES 3
AntoineC
Frequent Visitor

For anyone interested I managed to solve it. The trick was to get the measure to work with a date range while leaving the x-axis of the chart with my custom measure (Week number + first day of week). I had to include both those fields in the SELECTCOLUMNS and it works like a charm. See the corrected measure below:

Weekly Trend = 
VAR Known = FILTER(
        SELECTCOLUMNS(ALLSELECTED('T_DATES_GRAPH'[DATEP],'T_DATES_GRAPH'[Weekly X Axis]),"Known[X1]",'T_DATES_GRAPH'[DATEP],"Known[X2]",'T_DATES_GRAPH'[Weekly X Axis],"Known[Y]",[AGREGATED VALUE]),
        AND(NOT(ISBLANK(Known[X1])), NOT(ISBLANK(Known[Y])))
    )
VAR Count_Items = COUNTROWS(Known)
VAR Sum_X = SUMX(Known, Known[X1])
VAR Sum_X2 = SUMX(Known, Known[X1]^2)
VAR Sum_Y = SUMX(Known, Known[Y])
VAR Sum_XY = SUMX(Known, Known[X1] * Known[Y])
VAR Average_X = AVERAGEX(Known, Known[X1])
VAR Average_Y = AVERAGEX(Known, Known[Y])
VAR Slope = DIVIDE(Count_Items * Sum_XY - Sum_X * Sum_Y,Count_Items * Sum_X2 - Sum_X^2)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
AVERAGEX(DISTINCT('T_DATES_GRAPH'[DATEP]), Intercept + Slope * 'T_DATES_GRAPH'[DATEP])
v-yadongf-msft
Community Support
Community Support

Hi @AntoineC ,

 

Unfortunatelly, the function SUMX cannot work with values of type String.

 

Please change type of [Week] column to whole number:

vyadongfmsft_0-1669191683467.png

 

In my test, it works fine:

vyadongfmsft_1-1669191870144.png

 

Best regards,

Yadong Fang

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

Hi Yadong Fang,

 

Thanks for your answer. Unfortunately I can't use the Week Number as the x-axis in my chart. I absolutely need to use the week number and the first day of the week, for user experience purposes.

That's why I need to use my calculated column [Weekly Axis]

Weekly Axis = "W" & [WeekNumber] & " (" & FORMAT([DATEP],"dd/mm") & ")"

So I'm wondering if there is a way in my DAX trend mesure [Weekly Trend] to bypass this problem, maybe convert the string to an order integer?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.