RE: Need percentage column

[Anonymous]

Hi All,

 Zubair_Muhammad

i want to calucalte the  New column

1 .serviceorders/Service orders units    (show in %  two decimal)

2.Service orders+Service order units 

3.Service orders-Service order units 

i am new to PBI. 

so how to add new measures (caluclated measures )  can anyone guide the steps to perform the above measures?

Note:  i cannot   see how to  attach PBI file 😞

Best

Chanty

[AlB]

Hi @Anonymous

You have to upload the pbix somewhere else (Dropbox, OneDrive...) and share the URL here. Or upload the file to a site like tinyupload.com (no sign-up required)

Regarding your question. Do you need 1 or 3 calculated columns (since you mention 1. 2. 3.)?

Do you need measures or calculated columns, as you seem to be referring to both?

Cheers

[Anonymous]

Thanks for your Quick response.

i need  3 seperate columns      for each measure.

[AlB]

@Anonymous

To be able to help, I would need you to share the pbix or otherwise explain what 'serviceorders' and 'Service orders units' are. 

Are they measures?

If so please show the code.

If not, how did you build the visual you are showing? Are the columns you show for 'serviceorders' and 'Service orders units'  the Sum of those fields?Some other operation? 

It would also help if you show the structure of the table(s) in your data model.

If you can share the pbix that's the easiest/quickest.

[Anonymous]

Thanks.

i have started doing some sample on PBI  today only..    just loaded one excel file 

1. those are measure columns

2. i have only one table.

3. im unable to share pbix file...:(   its not allowed from this user.

Best

Chanty

[AlB]

@Anonymous

My answers/comments to your reply in red:

i have started doing some sample on PBI  today only..    just loaded one excel file 

1. those are measure columns

Ok but how did you build them? Is it by summarizing them using SUM in this menu? I need to know that to build the other measures. I mean, what the columns are showing now in your visual, is the SUM of 'Service Order Units' for every country? Is it also the SUM for 'Service Order ($)'?

2. i have only one table.

All right. Show it. Just go to the table in Power BI, click on it, use 'Copy table' and paste it here.

3. im unable to share pbix file...   its not allowed from this user

Like I said before, you have to host the file elsewhere and share the URL here. For instance go to tinyupload.com (no sign-up required) and upload your pbix file. You'll get an URL. Paste it here.

This is an example of a file that I uploaded:

http://s000.tinyupload.com/index.php?file_id=19863249533058382470

[Anonymous]

yeah its  sum(serviceorders)   by region only.

i used the same link to upload file but unable to do it. my company laptop not allowing to upload the fie.

RegionCountryParent WarrantyService Order ($)Hardware Order ($)Total Order($)O PRateService Order UnitsHardware Order UnitsTotal UnitsAttach Rate

RegionCountryParent WarrantyService Order ($)Hardware Order ($)Total Order($)O PRateService Order UnitsHardware Order UnitsTotal UnitsAttach Rate

RegionCountryParent WarrantyService Order ($)Hardware Order ($)Total Order($)O PRateService Order UnitsHardware Order UnitsTotal UnitsAttach Rate

EMEA	Belgium	Warranty Data Unavailable	41236.369	31531927.4445	31573163.8135	0.00130776556785439	1264	314112.531	315376.531	0.00402403557723713
EMEA	Belgium	3 Year Std Warranty	618015.5151	1216943.5369	1834959.052	0.507842390678463	9264.872	16700.002	25964.874	0.554782688049978
EMEA	Belgium	1 Year Std Warranty	581956.7203	147353.6936	729310.4139	3.94938671764655	5710.728	4089.674	9800.402	1.39637731516008
EMEA	Belarus	3 Year Std Warranty	1663.11	19915.2394	21578.3494	0.0835094154077806	187	90	277	2.07777777777778
EMEA	Belarus	1 Year Std Warranty	4870	7431.8798	12301.8798	0.655285086822852	487	77	564	6.32467532467532
EMEA	Azerbaijian	3 Year Std Warranty	150	60589.11	60739.11	0.00247569241403282	6	83	89	0.072289156626506
Asia Pacific	Bangladesh	3 Year Std Warranty	42800.79	156791.32	199592.11	0.272979333294726	427	8658	9085	0.0493185493185493
Asia Pacific	Bangladesh	1 Year Std Warranty	89711.4	2087.2598	91798.6598	42.980466542785	5245	390	5635	13.448717948718
EMEA	Belarus	Warranty Data Unavailable	0	1442609.2431	1442609.2431	0	0	5595	5595	0
EMEA	Baltics	Warranty Data Unavailable	0	-4752.2793	-4752.2793	0	0	30	30	0
EMEA	Baltics	3 Year Std Warranty	0	-1283.5612	-1283.5612	0	0	-2	-2	0
EMEA	Baltics	1 Year Std Warranty	0	-15879.5815	-15879.5815	0	0	-49	-49	0
EMEA	Azerbaijian	Warranty Data Unavailable	0	1422370.1965	1422370.1965	0	0	3050	3050	0
Asia Pacific	Bangladesh	Warranty Data Unavailable	0	8054278.82	8054278.82	0	0	148664	148664	0
EMEA	Belgium	UNCLASSIFIED BUNDLES	150620.5186	0	150620.5186	0	6259.216	0	6259.216	0
EMEA	Belarus	UNCLASSIFIED BUNDLES	2506.5525	0	2506.5525	0	112	0	112	0
EMEA	Azerbaijian	UNCLASSIFIED BUNDLES	1421.7296	0	1421.7296	0	73	0	73	0
EMEA	Azerbaijian	1 Year Std Warranty	54	0	54	0	3	0	3	0

[Anonymous]

yeah its  sum(serviceorders)   by region only.

i used the same link to upload file but unable to do it. my company laptop not allowing to upload the fie.

RegionCountryParent WarrantyService Order ($)Hardware Order ($)Total Order($)O PRateService Order UnitsHardware Order UnitsTotal UnitsAttach Rate

RegionCountryParent WarrantyService Order ($)Hardware Order ($)Total Order($)O PRateService Order UnitsHardware Order UnitsTotal UnitsAttach Rate

EMEA	Belgium	Warranty Data Unavailable	41236.369	31531927.4445	31573163.8135	0.00130776556785439	1264	314112.531	315376.531	0.00402403557723713
EMEA	Belgium	3 Year Std Warranty	618015.5151	1216943.5369	1834959.052	0.507842390678463	9264.872	16700.002	25964.874	0.554782688049978
EMEA	Belgium	1 Year Std Warranty	581956.7203	147353.6936	729310.4139	3.94938671764655	5710.728	4089.674	9800.402	1.39637731516008
EMEA	Belarus	3 Year Std Warranty	1663.11	19915.2394	21578.3494	0.0835094154077806	187	90	277	2.07777777777778
EMEA	Belarus	1 Year Std Warranty	4870	7431.8798	12301.8798	0.655285086822852	487	77	564	6.32467532467532
EMEA	Azerbaijian	3 Year Std Warranty	150	60589.11	60739.11	0.00247569241403282	6	83	89	0.072289156626506
EMEA	Algeria	Warranty Data Unavailable	-1636	1211397.6977	1209761.6977	-0.00135050611628713	2	5753	5755	0.000347644707109334
EMEA	Algeria	3 Year Std Warranty	2417.3253	-103027.6226	-100610.2973	-0.0234628853796341	126	-41	85	-3.07317073170732
EMEA	Algeria	1 Year Std Warranty	2646	-45940.88	-43294.88	-0.057595762205687	30	67	97	0.447761194029851
EMEA	Adriatic	Warranty Data Unavailable	22966.8495	16707310.7047	16730277.5542	0.00137465866924586	1584	152403	153987	0.0103934961910199
EMEA	Adriatic	3 Year Std Warranty	63881.4726	416606.0886	480487.5612	0.153337827621946	2019	5463	7482	0.369577155409116
EMEA	Adriatic	1 Year Std Warranty	262833.6089	89188.4950999999	352022.104	2.94694521535884	7682	3429	11111	2.24030329542141
Asia Pacific	Bangladesh	3 Year Std Warranty	42800.79	156791.32	199592.11	0.272979333294726	427	8658	9085	0.0493185493185493
Asia Pacific	Bangladesh	1 Year Std Warranty	89711.4	2087.2598	91798.6598	42.980466542785	5245	390	5635	13.448717948718
EMEA	Belarus	Warranty Data Unavailable	0	1442609.2431	1442609.2431	0	0	5595	5595	0
EMEA	Baltics	Warranty Data Unavailable	0	-4752.2793	-4752.2793	0	0	30	30	0
EMEA	Baltics	3 Year Std Warranty	0	-1283.5612	-1283.5612	0	0	-2	-2	0
EMEA	Baltics	1 Year Std Warranty	0	-15879.5815	-15879.5815	0	0	-49	-49	0
EMEA	Azerbaijian	Warranty Data Unavailable	0	1422370.1965	1422370.1965	0	0	3050	3050	0
Asia Pacific	Bangladesh	Warranty Data Unavailable	0	8054278.82	8054278.82	0	0	148664	148664	0
EMEA	Belgium	UNCLASSIFIED BUNDLES	150620.5186	0	150620.5186	0	6259.216	0	6259.216	0
EMEA	Belarus	UNCLASSIFIED BUNDLES	2506.5525	0	2506.5525	0	112	0	112	0
EMEA	Azerbaijian	UNCLASSIFIED BUNDLES	1421.7296	0	1421.7296	0	73	0	73	0
EMEA	Azerbaijian	1 Year Std Warranty	54	0	54	0	3	0	3	0
EMEA	Adriatic	UNCLASSIFIED BUNDLES	83624.1817	0	83624.1817	0	3889	0	3889	0

[AlB · Accepted solution]

@Anonymous

Ok, so let's try this, where Table1 is the name of your table:

1. Create these two measures: 

SUM_ServiceOrder($) = SUM(Table1[ServiceOrder($)])

SUM_ServiceOrderUnits = SUM(Table1[Service Order Units])

2. Using the two measures above, create the three measures you were asking for in your initial post:

a) serviceorders/Service orders units    (show in %  two decimal)

Measure1 = DIVIDE([SUM_ServiceOrder($)], [SUM_ServiceOrderUnits])

Under 'Modeling' you can set the format that you like (%, decimals etc)

b) Service orders+Service order units 

Measure1 = [SUM_ServiceOrder($)] + [SUM_ServiceOrderUnits]

c) Service orders-Service order units 

Measure1 = [SUM_ServiceOrder($)] - [SUM_ServiceOrderUnits]

3. Place the three newly created measures in values of the matrix visual, with Region and Country in rows of the matrix as you already had.    

[Anonymous]

Thanks alot for your steps 🙂 your helo is highly appreciated.

i have done the same way 🙂  before reading your reply.  

i normally added it worked.  then i saw your reply  its almost similar.

Anyway thanks Day1 is good to know so many things in PBI ..   

Best

Suresh