Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ahmed_Habib
Helper III
Helper III

Create a column with one distinct value from Matrix

Hey everyone,

Bellow I have a Matrix that is filtered by May. I have shown what I want and I have currently. What I want to do is I want to take the top most hiearchial value which in this case is 10% and create a new column in my Matrix that would include that one distint value in the column next to it. Say I filter to June and the value is 11% I want all the values in the column to say 11%. Eventually the goal is to conditional format between the two fields. Is this possible?

 

What I have:

May 
  Toronto Region10%
    Peel11%
      Store 121%
      Store 232%

 

What I want:

May  
  Toronto Region10%10%
    Peel11%10%
      Store 121%10%
      Store 232%10%

 

1 ACCEPTED SOLUTION

@Ahmed_Habib if that is a measure, try this

 

Measure 3 = CALCULATE ( [% Measure],  ALLEXCEPT( 'Table', 'Table'[Region] ) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

15 REPLIES 15
parry2k
Super User
Super User

@Ahmed_Habib create a measure 

 

 

Region Measure = 
CALCULATE( [Your % Measure], ALL ( Table[Region] ) )

 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sorry Parry there is no create function? Did you mean calculate?

Unfortunately it does not work. It just duplicates the values from the left side to the right side?

@Ahmed_Habib sorry not sure why I typed create, yes calculate, can you share your existing measure and also how you seeing it in matrix visual. What is on rows, what is on columns.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ahmed_Habib_0-1594845108703.png

Hi Parry i have crossed off sensitive data. But i basically need that 0.010% next to every value on the right side. Note this value changes as monthly filters occur.

FYI that ratio value is a measure so it does not live in a distinct column

@Ahmed_Habib so what happened if you use the measure I posted earlier.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

When I use your DAX script it just copies all the values on the left and puts them on the right. It does not display that one distinct value of 10%

@Ahmed_Habib also provide more information, I asked few questions, you just shared the screenshot and that was not answer to my questions? What is the measure? What you have on row/columns?

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

The measure is just dividing the Value 1/ Value 2.

Hi Perry, 

 

My appologies I missed that. In the rows It broken down to 3 levels(Region, District, Branch Name). I have no columns, I just have the three values you see.

@Ahmed_Habib are those measures? the column we are talking about is a measure? if yes, what is the expression? Or it is just a straight sum of columns? Oh man why it is so hard. sorry don't know how to help if I don't get the info I'm looking for



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

 

Value 1 = TOTALYTD(SUM(Column1_Reference),DimDate[Year Month],ALL(DimDate),"10/31")
Value 2 = is just sum of a column2_reference 
Ratio = Value 1/Value 2

@Ahmed_Habib if that is a measure, try this

 

Measure 3 = CALCULATE ( [% Measure],  ALLEXCEPT( 'Table', 'Table'[Region] ) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

HUGEEEE SUCESS YOU ARE A LIFE SAVER THANK YOU ❤️ 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.