Use Measure as a Column

[Anonymous]

Hello,

I have a requirement where in I have created three what if parameters and created measures for it as follows.

Measure 1: 

ABC Cogs =

Var A = SELECTEDVALUE('A Anlysis'[A Anlysis])

Var B = SELECTEDVALUE('B Analysis'[B Analysis])

Var C = SELECTEDVALUE('C Analysis'[C Analysis])

Return

IF(SUM(F3460WFC_Updated[Cumulative %])<=A,"A",IF(SUM(F3460WFC_Updated[Cumulative %])<=A+B,"B",IF(SUM(F3460WFC_Updated[Cumulative %])<=A+B+C,"C","C")))

Measure 2 ABC Inv =

Var A = SELECTEDVALUE('A Anlysis'[A Anlysis])

Var B = SELECTEDVALUE('B Analysis'[B Analysis])

Var C = SELECTEDVALUE('C Analysis'[C Analysis])

Return

IF(SUM(F41021WFC_Updated[Cumulative %])<=A,"A",IF(SUM(F41021WFC_Updated[Cumulative %])<=A+B,"B",IF(SUM(F41021WFC_Updated[Cumulative %])<=A+B+C,"C","C")))

After creating this measures I have to Concatenate this two meaures to show the analsysis as high to low.

Classification = CONCATENATE([ABC Cogs],[ABC Inv])

So my requiremet is I have to show this classification measure as a column and display the sales values in matrix chart where I can see sales amount based on Categories as "AA", "AB","AC","BA","BB","BC","CA","CB","CC".

Can anyone please help me how to achieve this as i have tried creating a column but it doesnt give me all the values as it is a text value.

Thanks 

Waseem

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

@Anonymous You can use this pattern that Avi Singh came up with.

https://www.youtube.com/watch?v=WV5KxZRIzUE

It uses a disconnected table with the columns that you want to appear on the rows/columns of a matrix.  Then a single measure uses multiple, nested SWITCH( TRUE() ) patterns to determine which measure to return in each "cell" of the matrix.

This sort of measure will have a performance hit, but it's the only easy(ish) way to solve problems like this.

Hope this helps,

~ Chris Haas

[v-alq-msft]

Hi, @Anonymous 

I think it is unsupported to use a measure in rows or columns in a matrix visual. Values in rows and columns should be determined. Even if measures and columns look similar, there is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user activity on the pivot table. A measure operates on aggregations of data defined by the current context. In a pivot table, for example, source tables are filtered according to the coordinates of cells, and data is aggregated and calculated using these filters. In other words, a measure always operates on aggregations of data under the evaluation context.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

If you want to pass values(using slicer) to measure and you want that as the column. Not possible. Because the column is pre-calculated. We can do with Static Values.

The other thing which can do force the context of a row on measure using summarize or values. So that calculation happens at a particular level. Check this link, how calculation have been forced at order level.

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

if you need more help mark me @

Appreciate your Kudos.

[Anonymous]

Thanks for your reply @amitchandak .

Is there any alternative so that i can convert a dynamic measure to a column and the use it in a matrix chart.

I have tried to create a new table with this three vaues as High, Low and medium but it doesnt fullfil my requiement.

I need to display some thing like this, But here the row labels as High Rotatio, Low Rotation, Medium Rotation are my measures.

Thanks

[amitchandak]

Let ask the Dax Guru.

Hi @Greg_Deckler , Can you help with this.

[Greg_Deckler]

In general, this is going to involve some form of the Disconnected Table Trick because you are trying to use a measure where you are not allowed to use a measure.

Beyond that, very difficult to tell without sample source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...