Forum Discussion

LauraBueno's avatar
LauraBueno
Helper III
6 years ago

Use Measure in x-axis

Hi,

 

I have a calculated measure that need to plot within the x-axis of an histogram, however I know measures cannot be used in x-axis of visuals. I was wondering whether there is a way to put the values of the calculated measure 'as a copy' in a column so that I can plot it?

 

Thanks

Laura

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Yes you can have a calculated column with a "measure" in it. Just verify how your context works, as when you convert a measure - that is supposed to work in a filter context - to a column - which has a row context - your measure will calculate in a different way.

    • LauraBueno's avatar
      LauraBueno
      Helper III
      6 years ago

      Hi Anonymous ,

       

      Thank you for your message. Would it work if I create a new table that has the correct columns so that when I put the expression within a column the evaluation per row is correct? If so how could I directly use the measure within the column (new _column = [Calculated_measure]? or do I need to put all the calculations within the column again? 

       

      Thanks 

      Laura

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        A measure if inserted in a Calculated Column will calculate the same value, but a measure is typically dynamic and recalculate based on the filter context. In a column there is no concept of a dynamic filter context.

        Example: imagine that you have a measure that calculate the sales amount over a period, and you have a date selector. Whenever you change the period, obviously that sales amount changes as it's calculated with the filter context determined by your date slicer. If you put that measure in a column, it will be calculated for each row of that table you've put it into and with the view of the data that that row has in that moment. So if you put it in the Sales table (where one row is one sold item) and the formula is Amount=SUMX(sales;sales[amount]) the measure will be always the overall total as rows are not filtered by the context.

        And yes, one way to work around this is to have a "precalculated measure" table, with a summarized view of your data. 
        However without knowledge of your data model is hard to recommend one or another method.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    While a measure cannot be used as the X-axis of a visual, it will help if you show your data and your expected result.  May be someone can suggest an alternative.

    • marker_bi's avatar
      marker_bi
      Regular Visitor
      6 years ago

      Hi,

       

      I am having a similar difficulty in that I am trying to create a line graph with a measure on the x-axis. I've seen the suggestions to use calculated columns instead but don't think that will work for my example.

       

      Attached is a simplified example of what I am trying to accomplish. On the tab, "Original" is shown a table of what I am trying to graph as well as a graph using R instead of Power BI's native line graph (as an aside, the reason I am not wanting to use the R/Python plotting capabilities is that for the amount of data i am working with, it takes a while for the graph to refresh each time i change the filters). One can think of it as ten years of profit from 3 different companies. I want to allow the user the select a single company, two companies, or all three, and then dynamically rank the 10 years of profit and graph them. Since profit is on the x-axis and it's range changes dependant on the filter sittings i'm not sure how to avoid using a measure.

       

      https://www.dropbox.com/s/9g64rnk2e03xklm/Power%20BI%20proof%20of%20concept%20v2.pbix?dl=0 

       

      Does anyone have suggestions?

      Thanks!