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 me...
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

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