Unpivot columns

[Anonymous]

Hi Everyone,

I am trying to create similar values in which i have few columns and i want to see if value in Key Figure column is equal to "Requested Sale Order" then it should only give me the values of two other coulmns called "Requested Shipped Order and "Requested Shipped Order Value" Similarly if key figure is equal to "Shipped Sales Order " then it should populate values of "Shipped Sales " and "Shipped Sales Order value " .

I try this in Matrix table but as soon as i enterd the values its showing me other columns as well ,please see below for better explanation :

Request you to please help me if its possible:

@amitchandak  @Ashish_Mathur  @Ahmedx @Bibiano_Geraldo 

Thanks,

Ashish

[MFelix]

Hi @Anonymous ,

This is related to the fact that you are adding measures to your matrix and when you place them in row they get repeated altough they do not have any values this is related with te context and the way the setup is done.

Can you please share the model setup?

[Anonymous]

Hi @MFelix ,

Thank you for your prompt resposne. Yes you are correct i have created measure for those but i have tried using those columns into "Values" field and still i am getting the same result .

I can share you images of my Model if it works for you.

Please see below and let me know if we can do something on this .

Regards,

Ashish

[MFelix]

Where does the Key figure column comes from?

Just to understand the full matrix setup

[Anonymous]

Hi @MFelix @amitchandak @Ahmedx @Ashish_Mathur 

Anything you can help me with. Its really blowing my mind .

Thanks,

Ashish

[MFelix · Accepted solution]

Hi @Anonymous ,

Sorry for the late reply create a new table with the following data:

Measure	Key Figure
Requestes_Sales_Order_Value	Requested Sales Order
Shiped_Sales	ShippedSalesOrder
Shiped_Sales_Order_Value	ShippedSalesOrder

Add the following measure to your model:

Measures for Matrix = SWITCH(SELECTEDVALUE('Key Figures'[Measure]), 
                            "Requestes_Sales_Order_Value", [Requestes_Sales_Order_Value],
                            "Shiped_Sales", [Shiped_Sales],
                            "Shipes_Sales_Order_Value", [Shipes_Sales_Order_Value])

Now use the columns on the matrix rows and the measure on the values:

If you want you can also add a ID column for the sorting of the data.

[Anonymous]

Hi @MFelix ,

I am not sure whether i got this or not. As you said create new table like this :

After that you said create new measure in your data model (in existing table CV_SOP) and whe i tried i am getting confused which table we need to refer in the formula. Please look below:

Now i am not sure which table i should refer to enter value ?

Can you please share the sampe PBIX?

Regards,

Ashish

[MFelix]

In this measure you need to erefer to the values from the new table and the metrics are the ones you are using.

In my example I called the table Key Measures. In your case you need to pick up the New_Table[Measure] not the New_Table[Key_Figure].

The Key_Figure is to be used on the matrix visualization not on the measure itself

[Anonymous]

Hi @MFelix ,

Its again blowing my mind as i am not able to create this formula .

If you see i have create a new Measure in my existing table (CV_SOP) and as soon as i entered column name "Booked _Sales-Order" and then i try to add Result its giving me error.

So which column it should come here"?

Can you check in above screen shot?

Thanks,

Ashish

[MFelix]

On this one you should place the measure that you want to calculate in this case believe is the total sales booked value.

Basically this measure will call obter measures when the specificied value of a column is on your visual. 

[Anonymous]

Hi @MFelix ,

i am really sorry to bother you again but i have tried creating the same measure and i got it but as soon as i am placing that Measure in Values its giving me blank value .
I tried entering the New Table[Key_Figure] in Row level with week number in Column but i am getting blank values there.

I am not sure what went wrong. Please see below images :

[MFelix]

To use this option the tables needs to be unrelated, delete the relationship between both tables and check if it returns what you need, you also need to add the measure column to the rows after the Key Figure (both of them must be from the new table).

[Anonymous]

Hi Miguel,

Finally i got it and i appreicate your time on this.

Thank you so much .

Kudos to you !!!

Regards,

Ashish