Data Representation

[lailailaieric]

Dear All   I have started to use PowerBI and I ran into this problem. I have looked online but seems like there are no solutions to it just yet.     As per below, these are the information I have.   

Place              Amount        Staff           Amount per Staff (Amount divided by Staff)

East China            10             11                      0.91

West China          20             30                       0.67

South China         62             50                      1.24

Asia                      17             25                      0.68

Total                   109            263                     0.41  

Does anyone how in PowerBI can I show the "0.41" as the answer?   I am only able to get the average, sum, count, etc amount per staff but that is not what I am looking for. I am looking for the total amount divided by total staff.   

Looking forward to hear from you pros out there. 

[Phil_Seamark]

Hi @lailailaieric

Have you tried adding a calculated measure to your table?

Something like this?

Amount Per Staff = 
	DIVIDE(
		SUM(Table2[Amount]) , 
		SUM(Table2[Staff])
		)

[lailailaieric]

Hi Phil

If thats the case, does it mean i have to use the table format again for this chart and I cant use other visuals?

Because the aim for me to when I click Asia, the number representating would appear, but when nothing is click "0.41" would show up. 

Thanks

[Phil_Seamark]

The measure should work as expected and show the correct number.

Give it a try 🙂

[lailailaieric]

Sorry Phil, 

Do you mind showing it once to me, step by step how you can type in the formula?

Because I am unable to locate the place to do so.

Thank you so much for your help. 

REally appreciating you helping me

[Phil_Seamark]

Hi @lailailaieric

Sure,  First of all make sure your table is highlighted,  Then from the modeling table, click the "New Measure" button

Then 2) enter the forumla I posted above in step 2 ) but change the name of Table2 to be the same name as your table 

[lailailaieric]

Hi @Phil_Seamark

 Thank you for your helpful teaching. 

Now that I have the new measures, would it be possible if I can do the same thing? Cause when I tried to add another measure, seems like they dont have this new measures as columns for me to pick from.

Thank you so much.  

[Eric_Zhang]

---

@lailailaieric wrote:

Hi @Phil_Seamark

 Thank you for your helpful teaching. 

Now that I have the new measures, would it be possible if I can do the same thing? Cause when I tried to add another measure, seems like they dont have this new measures as columns for me to pick from.

Thank you so much.  

---

Not sure what exactly you're trying to do, could you be more specific?

[lailailaieric]

Sorry for the confusion. 

Measure 1 and 2 came from different calculations in the excel. 

Therefore, I would like to ask is there a way to do calculations for measure 1 and 2 together?

I was not able to locate "Measures 1 or 2" when I was starting a new column. 

Hope this clears the confusion. 

[Eric_Zhang]

---

@lailailaieric wrote:

Sorry for the confusion. 

Measure 1 and 2 came from different calculations in the excel. 

Therefore, I would like to ask is there a way to do calculations for measure 1 and 2 together?

I was not able to locate "Measures 1 or 2" when I was starting a new column. 

Hope this clears the confusion. 

---

What is the calculation? Maybe you can create a third measure = measure1+measure2.

I'm not still clear about your situation, maybe you can upload a pbix with sample data and post the expected output.