cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lailailaieric Frequent Visitor
Frequent Visitor

Data Representation

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. 

9 REPLIES 9
Phil_Seamark Super Contributor
Super Contributor

Re: Data Representation

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])
		)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

lailailaieric Frequent Visitor
Frequent Visitor

Re: Data Representation

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 Super Contributor
Super Contributor

Re: Data Representation

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

 

Give it a try Smiley Happy


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

lailailaieric Frequent Visitor
Frequent Visitor

Re: Data Representation

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 Super Contributor
Super Contributor

Re: Data Representation

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 

 

add measure.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
lailailaieric Frequent Visitor
Frequent Visitor

Re: Data Representation

Hi @Phil_Seamark

 

Presentation1.jpg

 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.  

 

 

Moderator Eric_Zhang
Moderator

Re: Data Representation


@lailailaieric wrote:

Hi @Phil_Seamark

 

Presentation1.jpg

 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 Frequent Visitor
Frequent Visitor

Re: Data Representation

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. 

Moderator Eric_Zhang
Moderator

Re: Data Representation


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