Forum Discussion

AlienBI's avatar
AlienBI
Frequent Visitor
1 year ago
Solved

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

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?
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

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

    • AlienBI's avatar
      AlienBI
      Frequent Visitor
      1 year ago

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

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        1 year ago

        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.
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      AlienBI
      Frequent Visitor
      1 year ago

      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.

  • v-lgarikapat's avatar
    v-lgarikapat
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      AlienBI
      Frequent Visitor
      1 year ago

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

  • v-lgarikapat's avatar
    v-lgarikapat
    Icon for Community Support rankCommunity Support
    1 year ago

    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