Changing column graph displayed value

[albertgarri]

Hi everyone! 

I'm pretty new to power bi world, so my question might be obvious for most of you. 

My goal is to create an element similar to the one with the red box, that you can see on the following picture. But I don't know how should I do it. 

 My idea is that this "element" allow me to switch the value displayed on the colum graph. Thus, when someone clicks the price per unit box the graph will show "Price per unit" per "Food" data. 

My first guess was to use the slicer element, as I have done with the "Year" variable. However, the result obtained is not what I want. 

On next picture you can see the dataset I'm using (it's just a simple example of my real data). 

 Thank you very much for your support 🙂

Albert

[Baskar]

Cool .

1. Create one Table with that three columns like 

   1. Amount

   2. Unit price like ect.

2. Have to create one measure based on that selection on filter , pls look that image 

let me know if it is not helping u dude.

[v-huizhn-msft]

Hi @albertgarri,

Just as the @BetterCallFrank posted, his point is right. But miss a comma in his measure. I try to reproduce and get expected result.

Create a new table including the three column header name.  I name it Table3 shown in screenshot below.

Expected Value = 
IF(HASONEVALUE( Table3[Value]),
 SWITCH(VALUES( Table3[Value]),
 "Amount", SUM( Table4[Amount] ),
 "Amount spent", SUM( Table4[Amount spend] ),
 "Price per unit", SUM( Table4[Price per Unit] )
 ), "Please select only one value to display")

Then create clustered column chart, select the Food field as Axis, select the "Expected Value" as value level, you will get the right result. Please see the fisrt picture, when you select "Amount", it will display all the value of different food. While when clicks the "Price per unit" box , the graph will show "Price per unit" per "Food" data shown in the second picture.






If you have any other problem, please feel free to ask.

Best Regards,
Angelia

[albertgarri]

Thank you very much for all your fast responses. I'm pretty sure this would have solved a more advanced user problem. 
However, I'm still very new with power BI and I do not dominate some of the basic tools.

I've tried to follow your instructions but I got stuck at the 1st step. I don't know how to create the table with the three column header name. Can you give me some indications?  

Again, thank you so much for the help 🙂 

@BetterCallFrank
@Baskar
@v-huizhn-msft

[v-huizhn-msft]

Hi @albertgarri,

In your PowerBI desktop interface, click New Table(highlighted in red line) under Modeling window, you will get the second screenshot, please type the code in the location where cursor blinks(highlighted in yellow background).

If you have any problem, please feel to ask.

Best Regards,
Angelia

[albertgarri]

Hi @v-huizhn-msft

Thank you againg for your help 🙂

However, I haven't been able yet to success on my attempt. 

These have been my steps and my result (on the picture below): 
1) Click New Table

2) Writting : 

Table 4 = Expected Value =
IF(HASONEVALUE( Table3[Value]);
SWITCH(VALUES( Table3[Value]);
"Amount"; SUM( Table4[Amount] );
"Amount spent"; SUM( Table4[Amount spend] );
"Price per unit"; SUM( Table4[Price per Unit] )
); "Please select only one value to display")

3) I got the syntax error

Reading the previous messages I believe that my problem is that I do not know how to create the 3 column header name (table 3) (see, the squared area on the following pict). 



Thank you so much! 

[v-huizhn-msft · Accepted solution]

Hi @albertgarri,

I am sorry for inconvenience, I misunderstand your idea in above reply. The solution above is to create a new table using formula. While in this solution, please click the Enter Date, you will get the create table window, please type the values, you can rename the table name in highlighted background section. Click OK  you get Table4.

In the DAX formula, "Expected Value" is the measure name. Please right click your table->New measure, type the following statement is over-all.

Expected Value = 
IF(HASONEVALUE( Table3[Value]),
 SWITCH(VALUES( Table3[Value]),
 "Amount", SUM( Table4[Amount] ),
 "Amount spent", SUM( Table4[Amount spend] ),
 "Price per unit", SUM( Table4[Price per Unit] )
 ), "Please select only one value to display")

In your scenario, please replace Table3 name using your actual table name. Table4 is the new table name. Please replace it using your table name.

If you have any other issue, please let me know

Best Regards,
Angelia

[ivancespedesl]

Does this work only for one table at a time? I tried plugging different variables from different tables and it stopped working.

[albertgarri]

Thank you very much! 

I've been able to make it work! Really appreciatte all your help! 🙂

[BetterCallFrank]

Hi @albertgarri

what you describe is possible, however not the most easy beginner topic 😉

here's what needs to be done:

1. create helper table without relationship with 1 col, 3 rows

Value

----

Amount

Amount Spent

Price per Unit

2. create a measure in your table1 e.g.

DisplayValue =
IF(HASONEVALUE( 'Value'[Value])
  SWITCH(VALUES( 'Value'[Value] ),
    "Amount", SUM( Table1[Amount] ),
    "Amount Spent", SUM( Table1[Amount Spent] ),
    "Price per Unit", SUM( Table1[Price per Unit] )
  ), "Please select only one value to display")

something similar will do the trick for you.

3) pull new measure "DisplayValue" into your visual intead of "Amount" etc.

HTH,

Frank

[ivancespedesl]

Hi,

I followed all the steps and got everything correct. However, i want to get more information and I would like to have some MoM%, average, and other kind of calculations for all these measures. I have tried to do it using time intelligence in quick measure, but these seem not to work.

Could you please help me to make some calculations with this "macro" measure?

Regards,
IC