My calculated column doesn't work with context when using it on a line chart

[AlienBI]

Hi there, 

Relatively new to PowerBI - I've got a little issue hope you can help me with. 
I created a calculated column called RPD as such:

RPD = CALCULATE(DIVIDE(sumx('DemandPlotterRA+RES',DISTINCT('DemandPlotterRA+RES'[T&K])),sumx('DemandPlotterRA+RES',DISTINCT('DemandPlotterRA+RES'[Days])))

)

Now this one does what it is supposed to and creates the RPD measure for every row in my table:

But when I try to plot it on my line chart as an average, it gives me the same number for every calendar date:

FYI the black line in my chart if a calculated measure called OnRent as such:

OnRent =

VAR tmpOnRent = ADDCOLUMNS('DemandPlotterRA+RES',"Pick up date", 'DemandPlotterRA+RES'[PU date],"Drop off Date",'DemandPlotterRA+RES'[DO date])

VAR tmpTable =  

SELECTCOLUMNS(

    FILTER(

        GENERATE(

            tmpOnRent,

            'Calendar'

        ),

        [Date] >= [PU date].[Date] &&

        [Date] <= [DO date].[Date]

    ),

    "ID",if(isblank('DemandPlotterRA+RES'[Confirmation Number]),'DemandPlotterRA+RES'[RA Number],'DemandPlotterRA+RES'[Confirmation Number]),

    "Car class",'DemandPlotterRA+RES'[Car class],

    "Pick up location",'DemandPlotterRA+RES'[PU Location],

    "Date",[Date]

)

VAR tmpTable1 = GROUPBY(tmpTable,[ID],[Car class],[Pick up location],"Count",COUNTX(CURRENTGROUP(),[Date]))

RETURN COUNTROWS(tmpTable1)

And the pink line refers to a fleet line in a different table. 

What am I doing wrong?

[v-lgarikapat]

Hi @AlienBI ,

I wanted to follow up and confirm whether you’ve had the opportunity to review the information  provided by @Ashish_Mathur , @danextian  If you have any questions or need further clarification, please don’t hesitate to reach out.

we would be glad to support you.

Best Regards,

Lakshmi

[v-lgarikapat]

Hi @AlienBI ,

Thanks for reaching out to the Microsoft fabric community forum.

@danextian ,

@Ashish_Mathur 

Thanks for your prompt response

@AlienBI 

Could you please provide sample data that fully represents the issue or question you're referring to? Kindly ensure the data is in a usable format (e.g., Excel or CSV) rather than a screenshot, and does not contain any sensitive or unrelated information.

Looking forward to your response.

Best regards,
Lakshmi Narayana

[AlienBI]

Thanks for your help, I've now shared the link to download the pbix

[danextian]

The possible reason is you created a calcualted column instead of a measure. I don't see repeating numbers in RPD your screenshot so it may not be the reason as well but then what we see is just a chunk of the actual table. It would be easier if we had a copy fo the pbix and then you explain to us what your expected results are for some of the dates.

[AlienBI]

Yes I did create a calculated column instead of a measure. I tried with a measure but I had a similar result - it wasn't changing with context, the same value applied to all.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Which of those lines if the problematic one - green or red?

[AlienBI]

Sorry first time here! Green line is the issue.
Here's the link

[Ashish_Mathur · Accepted solution]

Hi,

Try these steps:

1. In Power Query, create a master list of all unique RegionClass entries
2. Create a Many:1 relationship from the Regionclass column of DemandPlotterRA table and Fleet-All Locations table to the table created in 1 above
3. Delete the relationship between DemandPlotterRA table and Fleet-All Locations table
4. Create a relationship between the relevant date column of the DemandPlotterRA table to the Calendar table.