Create a column with one distinct value from Matrix

[Anonymous]

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 Region	10%
Peel	11%
Store 1	21%
Store 2	32%

What I want:

May
Toronto Region	10%	10%
Peel	11%	10%
Store 1	21%	10%
Store 2	32%	10%

[parry2k]

@Anonymous 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.⚡

[Anonymous]

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

[Anonymous]

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

[parry2k]

@Anonymous 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.⚡

[Anonymous]

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.

[Anonymous]

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

[parry2k]

@Anonymous 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.⚡

[Anonymous]

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%

[parry2k]

@Anonymous 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.⚡

[Anonymous]

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

[Anonymous]

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.

[parry2k]

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

[Anonymous]

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

[parry2k · Accepted solution]

@Anonymous 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.⚡

[Anonymous]

HUGEEEE SUCESS YOU ARE A LIFE SAVER THANK YOU ❤️