Forum Discussion

justinMcC's avatar
justinMcC
Frequent Visitor
8 years ago
Solved

Turning a measure into a column

Hi,

 

I have a calculated measure on a table in PowerBi desktop report to categorise holdings as 'dairy' or 'beef' 

 

However I need to turn the data in table into a chart and I cannot use the measure on the legend or axis on chart. Below is the sample date:

 

HOLDINGID       CATEGORY (measure)

holding_1          dairy

holding_2          beef

holding_3          beef

 

On the chart I want to show Beef = 2 and Dairy = 1

 

How can I achieve this? can you turn a measure into a custom column?

 

Thanks for your help

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi justinMcC,

     

    Here is two solutions based on your data and WolfBiber's idea.

    1. Calculated table.

    CalculatedTableM1 = 
	ADDCOLUMNS(
		SUMMARIZE('Table1', 
				Table1[HoldingID], 
				"SumPercent_dairy", sum(Table1[percent_dairy])),
				"Category", if([SumPercent_dairy]>=80, "DAIRY","BEEF"))

    2. Measure.

    1) Create a new table "Category" by "Enter data" with data "Beef" and "Dairy". (don't create any relationships)

    2) Create a measure.

    Measure = var cat = min('CategoryTableM2'[Category]) 
	return sumx(
		ADDCOLUMNS(
			SUMMARIZE('Table1', 
				Table1[HoldingID], 
				"sumTemp", sum(Table1[percent_dairy])), 
				"categoryTemp", if([sumTemp]>=80,"DAIRY","BEEF")),
				if([categoryTemp] = cat, 1,0))

    Turning_a_measure_into_a_column

     

    Best Regards,

    Dale

9 Replies

  • WolfBiber's avatar
    WolfBiber
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hey,

    you can add a calculated column.

    A measure isnt 'bound' to a table, so if you want just to categorize your datarows, add a calculated column.

    For any more help, please provide a pbix or at least some DAX.

    Greetings.

    • justinMcC's avatar
      justinMcC
      Frequent Visitor
      8 years ago

      Hi WolfBiber

       

      I have a table called 'holdings' with 2 measures to calculate if a holding percent_dairy >= x the 'dairy' else 'beef'

       

      Measure 1:

      Calculate_Dairy = CALCULATE(SUMX(holdings, holdings[percent_dairy]),ALLEXCEPT(holdings, holdings[holdingid]))

       

      Measure 2:

      Category = IF([Calculate_Dairy] >= [bvd_percent_2], "DAIRY","BEEF")

       

      Table output example, if eg 'percent_dairy' >= 80

       

      HOLDINGID         PERCENT_DAIRY       CATEGORY

      holding_1            81                              DAIRY

      holding_2            22                              BEEF

      holding_3            44                              BEEF

       

      Summary for chart:

       

      Beef = 2, Dairy = 1

       

      I tried putting the calculation in measure 2 into a calculated column, however all the results were listed as 'beef' regardless of 'percent_dairy' value

       

      Any help you could give would be much appreciated

       

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi justinMcC,

     

    Here is two solutions based on your data and WolfBiber's idea.

    1. Calculated table.

    CalculatedTableM1 = 
	ADDCOLUMNS(
		SUMMARIZE('Table1', 
				Table1[HoldingID], 
				"SumPercent_dairy", sum(Table1[percent_dairy])),
				"Category", if([SumPercent_dairy]>=80, "DAIRY","BEEF"))

    2. Measure.

    1) Create a new table "Category" by "Enter data" with data "Beef" and "Dairy". (don't create any relationships)

    2) Create a measure.

    Measure = var cat = min('CategoryTableM2'[Category]) 
	return sumx(
		ADDCOLUMNS(
			SUMMARIZE('Table1', 
				Table1[HoldingID], 
				"sumTemp", sum(Table1[percent_dairy])), 
				"categoryTemp", if([sumTemp]>=80,"DAIRY","BEEF")),
				if([categoryTemp] = cat, 1,0))

    Turning_a_measure_into_a_column

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thaks for the amazing solution. I am very new to power bi and just implmented this solution.  However when i apply the same logic to my data, the slicer filters are not affecting on this table. The values remain constant in this. Is this how it works?  kindly suggest..thanks in advance

    • quickcreator's avatar
      quickcreator
      Icon for Helper I rankHelper I
      6 years ago

      Would that work for this? I'm trying to get a monthly cummulative % total. ProductX does not work, as it's a measure from the data of Cash & Asset tables. Here are some screen shots.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Guys,

     

    Hope all are doing good,

       I'm also facing the same issue from last few weeks onwards,But this is the basic thing but i'm unable to findout the way for below issue.

     

    Percentage DAX :-

    COUNT('Table'[Name])/CALCULATE(COUNT('Table'[Name]),ALLSELECTED('Name'))
    Name Percentages
    A24.36%
    B20.51%
    C15.30%
    D8.97%
    E6.41%
    F6.41%
    G6.41%
    H5.13%
    I3.85%
    J2.56%

     

    I want to display the categories based on percentages cutoff's

    IF([Percentage]>= 10, "High","Low")
    By default it was showing correct result only.But when  i was tried to apply some date range in filters  by using [Date] column .That time If function is not working.please check in below image.
     
    ALow22.58%
    BHigh19.35%
    CLow16.13%
    DLow16.13%
    ELow12.90%
    FLow9.68%
    GHigh3.23%

     

    If i write the same IF DAX by using CALCULATED MEASURE.It was showing correct result in all scenarios but i want to display as Pie chart. measures can't take in  Legend  right.

     

    Appriciate for any help.

     

    Regards,

    Dinesh.