% of total not working when using % of total in slider.

[Vigneshkris]

Hi All,

Can you please help me in solving the below mentioned issue  

I have the table with the following details.
Investor_Name	Company	Invest Type	Quantity	Cost Price	Market Price	Total_Cost	Market Value	% of total ( Calculated field)
AAAA	XXXX	Liquid	100	1	2	100	200	1%
AAAA	YYYY	MF	200	2	3	400	600	2%
AAAA	ZZZZ	Equity	300	3	4	900	1200	5%
AAAA	TTTT	Equity	400	4	6	1600	2400	9%
AAAA	SSSS	Equity	500	5	9	2500	4500	15%
BBBB	XXXX	Liquid	50	6	12	300	600	2%
BBBB	YYYY	MF	100	7	13	700	1300	4%
BBBB	ZZZZ	Equity	150	8	14	1200	2100	7%
BBBB	TTTT	Equity	200	9	15	1800	3000	11%
BBBB	SSSS	Equity	250	10	16	2500	4000	15%
CCCC	XXXX	Liquid	25	11	17	275	425	2%
CCCC	YYYY	MF	50	12	18	600	900	4%
CCCC	ZZZZ	Equity	75	13	19	975	1425	6%
CCCC	TTTT	Equity	100	14	20	1400	2000	8%
CCCC	SSSS	Equity	125	15	21	1875	2625	11%
						17125
In my report I am trying to pull columns Investor Name , Invest Type , Quantity , Cost Price , Market Price , Tota lCost , Market Value
Investor_Name	Invest Type	Sum of Quantity	Total_Cost	Market Value	% of Total
AAAA	Equity	1200	5000	8100	29%
AAAA	Liquid	100	100	200	1%
AAAA	MF	200	400	600	2%
BBBB	Equity	600	5500	9100	32%
BBBB	Liquid	50	300	600	2%
BBBB	MF	100	700	1300	4%
CCCC	Equity	300	4250	6050	25%
CCCC	Liquid	25	275	425	2%
CCCC	MF	50	600	900	4%
			17125
If I use the Sum of Profit Margin ( created a new column )as Slider and filter for % of Total > 10 %  is not getting displayed properly as shown below
Investor_Name	Invest Type	Sum of Quantity	Sum of Total_Cost	Sum of Market Value	% of Total
AAAA	Equity	1200	5000	8100	34%
BBBB	Equity	600	5500	9100	37%
CCCC	Equity	300	4250	6050	29%
			14750
How ever  What I am looking for is . The reason is every time I use a slider the % of total is recalculated based on the filter and I just want the calculations to be static.
Investor_Name	Invest Type	Sum of Quantity	Sum of Total_Cost	Sum of Market Value	% of Total
AAAA	Equity	1200	5000	8100	29%
BBBB	Equity	600	5500	9100	32%
CCCC	Equity	300	4250	6050	25%

[v-jiascu-msft]

Hi @Vigneshkris,

Actually, we need to filter the result of "sum of % of Total" in your scenario. Since a measure can't be used in a slicer, there could be two workarounds.

1. Use the Filter Level filter. 

2. Add a new column like below.

Column =
CALCULATE (
    SUM ( Table1[% of total ( Calculated field)] ),
    ALLEXCEPT ( Table1, Table1[Investor_Name], Table1[Invest Type] )
)

Best Regards,

Dale

[Vigneshkris]

Thanks Dale for the response. One Quick question : what will be formula to be used for calculating % of Total (Calculated Field). 

I used the default option Show Value as - > Percentage of Grand Total option available. However I am not able to view the formula used. 

[v-jiascu-msft · Accepted solution]

Hi @Vigneshkris,

That's an original column. So there isn't a formula exists. And "Show Value as - > Percentage of Grand Total" is a built-in function. No formula is needed. If you want to know, it could be like below. 

Percentage = divide(sum('table'[% of Total]), calculate(sum('table'[% of Total]), all('table')), 0)

Best Regards,

Dale

[Vigneshkris]

Hi Dale , Sorry to bug you again. 

In this formula provided , 

Column =
CALCULATE (
    SUM ( Table1[% of total ( Calculated field)] ),
    ALLEXCEPT ( Table1, Table1[Investor_Name], Table1[Invest Type] )
)

 I am not sure how to write this formula. Is % of total a function  ? 

SUM ( Table1[% of total ( Calculated field)] )

[v-jiascu-msft]

Hi @Vigneshkris,

That's a column name. I copied from your first post.

Best Regards,

Dale

[Vigneshkris]

Any pointers or link should be of great help. Thanks