Forum Discussion

jimmyswoosh's avatar
jimmyswoosh
Advocate I
7 years ago
Solved

Alternative Approaches To Referencing Calculated Measure in a Calculated Column?

Hi Power BI Community!   Goal: To be able to identify outlier values from the dataset dynamically and have them highlighted in the table.   Approach: I have created a calculated measure 'MTTCA_...
Need Help
  • jimmyswoosh's avatar
    jimmyswoosh
    7 years ago

    I was able to accomplish what I needed by creating a measure for each metric value and a measure for each metric's outlier threshold.

     

    Then I created a measure, using switch to compare the metric value to the threshold and return either "Outlier" if it's above the threshold or "Not Outlier" if it's below or equal to the threshold.

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, jimmyswoosh

Calculate table and calculate column all affected by any slicer or other visuals on the report. they are only affected by

the database refresh. So you can't compare measure with column.

The only way is that you create a measure again and then compare them.

 

Best Regards,

Lin

 

  • jimmyswoosh's avatar
    jimmyswoosh
    Advocate I
    7 years ago

    Thanks v-lili6-msft for the reply,

     

    For clarification on my part, are you saying that there's no way to acheive a workaround solution since we can't compare measure in a column?

     

    -James

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi, jimmyswoosh

      the result of measure  is dynamic and the result of column is static, so they can't be compared in together.

      It is usually that column and column comparisons or measure and measure comparisons.

       

      Best Regards,

      Lin

      • jimmyswoosh's avatar
        jimmyswoosh
        Advocate I
        7 years ago

        Thanks for the reply v-lili6-msft, I'm aware of the limitation of unable to compare measure to column. I'm trying to see if there's an alternative approach, not using the measure to column comparison to still achieve what I need.

         

        Hi Greg_Deckler,

         

        Sorry for the sudden loop in. I was reading through one of your posts on dynamic ABC classification (https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146). I thought my issue here is pretty similar and wanted to get your take on it.

         

        I have a dataset of values, I created measures to dynamically calculate outlier threshold aka. upper boundary. Now I want to add a column to the table where it says "Outlier" or "Not Outlier" and dynamically change as we slice by different attributes. Issue is that we can't do a column to measure comparison.

         

        Looking at your solution to Dynamic ABC Classification, I was wondering if we could do something similar:

        1. Create a measure
        2. Create variables to create a copy of the main table, add column [IsOutlier] and do the comparison in there
        3. Use a switch case function to either return "Outlier" or "Not Outlier" to the column [IsOutlier]
        4. Return the table
        5. Plot the measure in the "Values" field in a table visualization.

         

        Edit [10/25/2018]

        Looking at Greg_Deckler's DAX some more and I realized that the values you used are hard coded in and not dynamic since you're comparing "[__CumulatedPercentage]" to 0.7 and 0.9. Aside from that, do you have any ideas how we can still achieve what I need?

         

         

        mABC Class = 
VAR __salesTable = ADDCOLUMNS(ALLSELECTED('Sales SalesOrderDetail'),"__TotalSale",[OrderQty]*[UnitPrice])
VAR __salesTable1 = GROUPBY(__salesTable,[ProductID],"__ProductSales",SUMX(CURRENTGROUP(),[__TotalSale]))
VAR __salesTable2 = ADDCOLUMNS(__salesTable1,"__CumulatedSales",SUMX(FILTER(__salesTable1,[__ProductSales]>=EARLIER([__ProductSales])),[__ProductSales]))
VAR __totalProductSales = SUMX(__salesTable1,[__ProductSales])
VAR __salesTable3 = ADDCOLUMNS(__salesTable2,"__CumulatedPercentage",DIVIDE([__CumulatedSales],__totalProductSales,0))
VAR __salesTable4 = ADDCOLUMNS(__salesTable3,"__ABC Class",SWITCH(TRUE(),[__CumulatedPercentage]<=0.7,"A",[__CumulatedPercentage]<=0.9,"B","C"))
VAR __salesTable5 = FILTER(__salesTable4,[ProductID] = MAX('Production Product'[ProductID]))
RETURN MAXX(__salesTable5,[__ABC Class])

         

         

        -James