How to remove % column in front of total column in Pivote table

[P_Kale]

Hi,

Count({< EARLY_NON ={'EARLY','NON EARLY'}>} POLICY_NO)/Count(TOTAL <FY_YEAR> {< EARLY_NON ={'EARLY','NON EARLY'}>} POLICY_NO) * 100

through above formula i am able to calculate % row wise against total but the formula is getting applied to total column also and 100 is appearing in front of all the rows. I not want this % column in total. How I can remove this.

EARLY_NON	Totals		EARLY		NON EARLY
FY_YEAR	NOP	%	NOP	%	NOP	%
Totals	28200	100	13759	58.69129	14441	41.30871
FY_2015-2016	12000	100	2982	47.26581	9018	52.73419
FY_2016-2017	3400	100	2809	51.70256	591	48.29744
FY_2017-2018	5000	100	2207	53.85554	2793	46.14446
FY_2018-2019	6000	100	2953	70.67975	3047	29.32025
FY_2019-2020	1800	100	2808	81.9854	-1008	18.0146

Thanks in advance

@marcus_sommer 

@swuehl 

@sunny_talwar 

@Anil_Babu_Samineni 

[P_Kale]

Thanks @Anil_Babu_Samineni  for sending the application.

It is exactly what I am looking for.

But when I am applying it on original data it is not working properly.

In below given table EARLY & NON EARLY NOP values are correct but Total of NOP's is wrong secondly % also wrong. Ideally total should be (Early + Non Early) i.e. for FY 2016-2017 - 600, and so on. I have used below formula. What I am assuming is Total what I am getting is total of all the policies for FY 2016-2017.

If(EARLY_NON='EARLY' and Measure='NOP', Count([POLICY_NO]),
If(EARLY_NON='EARLY' and Measure='NOP%', Num(Count([POLICY_NO])/Count(TOTAL [POLICY_NO]), '#.#0%'),
If(EARLY_NON='NON EARLY' and Measure='NOP', count([POLICY_NO]),
If(EARLY_NON='NON EARLY' and Measure='NOP%', Num(Count([POLICY_NO])/Count(TOTAL [POLICY_NO]), '#.#0%'),
If(Measure='NOP', Count(POLICY_NO)
)))))

	EARLY_NON	-	EARLY		NON EARLY
FY_YEAR	Measure	NOP	NOP	NOP%	NOP	NOP%
FY_2016-2017		211884	500	0.10%	100	0.10%
FY_2017-2018		219116	2300	0.10%	456	0.10%
FY_2018-2019		208958	100	0.10%	29	0.10%
FY_2019-2020		192505	300	0.10%	120	0.00%
FY_2020-2021		243899	567	0.10%	45	0.00%
FY_2021-2022		256389	90	0.10%	-	-
FY_2022-2023		299756	356	0.00%	-	-
FY_2023-2024		226406	987	0.00%	-	-

[Anil_Babu_Samineni]

@P_Kale We are here an assumption until you share the file to test with.

Anyway, Can you please confirm if you have added for this field "EARLY_NON" and loaded Dimension table?

[johnmonson]

Thanks @Labels_UT  I'm working with the following:

Try this

LOAD [Policy Number],
Claim_Type,
FY_YEAR
FROM
[SAMPLE_DATA.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD FY_YEAR,
Count([Policy Number]) as Total
FROM
[SAMPLE_DATA.xlsx]
(ooxml, embedded labels, table is Sheet1)
Group by FY_YEAR;

[P_Kale]

HI @Anil_Babu_Samineni 

I have already loaded Dimension table the script only thing is formula was wrong which i have corrected now so i am getting "%" and "count" properly. Everything is correct  except now only thing is I am not getting "Total NOP" count in the table. i.e. Early + Non Early.

Below given is the formula.

If(EARLY_NON='EARLY' and Measure='NOP', Count({< EARLY_NON ={'EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(EARLY_NON='EARLY' and Measure='NOP%', num(Count({< EARLY_NON ={'EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO)/Count(TOTAL <FY_YEAR> {< EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO),'##.0%'),
If(EARLY_NON='NON EARLY' and Measure='NOP', Count({< EARLY_NON ={'NON EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(EARLY_NON='NON EARLY' and Measure='NOP%', num(Count({< EARLY_NON ={'NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO)/Count(TOTAL <FY_YEAR> {< EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO),'##.0%'),
If(Measure='NOP', Count({< EARLY_NON ={'EARLY','NON EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO)
)))))

	EARLY_NON	EARLY		NON EARLY
FY_YEAR	Measure	NOP	NOP%	NOP	NOP%
Totals		15536	67.3%	7535	32.7%
FY_2015-2016		2656	53.0%	2356	47.0%
FY_2016-2017		2499	56.8%	1898	43.2%
FY_2017-2018		1924	56.9%	1459	43.1%
FY_2018-2019		2434	68.1%	1138	31.9%
FY_2019-2020		2296	79.6%	590	20.4%
FY_2020-2021		1978	95.5%	94	4.5%
FY_2021-2022		1095	100.0%	-	-
FY_2022-2023		551	100.0%	-	-
FY_2023-2024		103	100.0%	-	-

[Anil_Babu_Samineni]

@P_Kale For me this sounds, you don't have calculated dimension? And I have disabled few checkbox (Like, Addons null values). Hope you have used the same settings? 

[P_Kale]

Hi @Anil_Babu_Samineni  you are simply great. Just looking at table you able to figure it out something is missing in dimension. You are right I have missed "=Pick(Dim, 'Total', EARLY_NON)" this in dimension.

Now I am getting "Total" as a column. But, now "%" column is coming because  "NON EARLY" values are not available & values are available in "EARLY" column. How i can remove this.

	EARLY_NON EARLY	EARLY		NON EARLY		Total
FY_YEAR	Measure	NOP	NOP%	NOP	NOP%	NOP	NOP%
Totals		15529	67.4%	7523	32.6%	23052	-
FY_2015-2016		2656	53.0%	2356	47.0%	5012	-
FY_2016-2017		2499	56.8%	1897	43.2%	4396	-
FY_2017-2018		1924	56.9%	1456	43.1%	3380	-
FY_2018-2019		2434	68.2%	1136	31.8%	3570	-
FY_2019-2020		2296	79.7%	586	20.3%	2882	-
FY_2020-2021		1976	95.6%	92	4.4%	2068	-
FY_2021-2022		1092	100.0%	-	-	1092	100.0%
FY_2022-2023		550	100.0%	-	-	550	100.0%
FY_2023-2024		102	100.0%	-	-	102	100.0%

[Anil_Babu_Samineni]

@P_Kale I could see you have some null values in Non Early. So, Did you disabled this?

[P_Kale]

Hi @Anil_Babu_Samineni 

Yes It is disabled only. Screen shot is attached.

[P_Kale]

Hi, @Anil_Babu_Samineni 

Yes it is disabled screen shot is attached.

Thanks & Regards,

[Anil_Babu_Samineni]

@P_Kale Please share again the QVf to test with. I guess only the difference from your app and my app is, You are having some data for %NOP, Could be the reason that you can still see that.