Turning a measure into a column

[justinMcC]

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

[Anonymous]

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
A	24.36%
B	20.51%
C	15.30%
D	8.97%
E	6.41%
F	6.41%
G	6.41%
H	5.13%
I	3.85%
J	2.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.

A	Low	22.58%
B	High	19.35%
C	Low	16.13%
D	Low	16.13%
E	Low	12.90%
F	Low	9.68%
G	High	3.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.

[v-jiascu-msft]

Hi @justinMcC,

Could you please mark the proper answer as solution?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

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))

Best Regards,

Dale

[quickcreator]

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]

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

[Greg_Deckler]

You could potentially use the disconnected table trick as explained here:

https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[WolfBiber]

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]

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

[WolfBiber]

Hey,

maybe a calculated table is the right way for your purpose.

Find a entry point here :

https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables