cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Use Measure as a Column

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
 
7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Use Measure as a Column

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Use Measure as a Column

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

 

 

 

 

Screenshot (31).png

Highlighted
Super User IV
Super User IV

Re: Use Measure as a Column

Let ask the Dax Guru.

 

Hi @Greg_Deckler , Can you help with this.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Use Measure as a Column

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Use Measure as a Column

Hi, @waseem24 

 

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.

Highlighted
Super User IV
Super User IV

Re: Use Measure as a Column

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Anonymous
Not applicable

Re: Use Measure as a Column

@waseem24 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

 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors