DAX Calc.

[sdhn]

Hi All,

I have  a Source SS as below.

I want to remove last row total and wants to calculate values thru Power Bi.

There are  formulas in the total of Column E  & I as:

Total : Column" E" 43:

=IF(D43<=0,"N/A",(C43/D43))

Total: Column "1 "43:

=IF(H43>0,(H43/D43),"N/A")

I need help here.

Do I need write  measure? if yes, how to start DAX for if etc.

help will be appreciated. Thanks 

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, I can roughly understand your requirement, I think you can try to achieve this by creating 3 measures for the columns E, H, and I:

Column E =

var _D43=SUMX(ALL('Table'),'Table'[D])

var _C43=SUMX(ALL('Table'),'Table'[C])

return

IF(_D43<=0,"N/A",DIVIDE(_C43,_D43))

Column H =

var _F43=SUMX(ALL('Table'),'Table'[F])

var _G43=SUMX(ALL('Table'),'Table'[G])

return

_F43+_G43

Column I =

var _H43=SUMX(ALL('Table'),'Table'[D])

var _D43=SUMX(ALL('Table'),'Table'[C])

return

IF(_H43>0,"N/A",DIVIDE(_H43,_D43))

And you can create a table chart to place the measures to get what you want, like this:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sdhn]

Column 😧  =(B2+C2) to  Row 43:  =SUM(B43:C43)

Column E :  =IF(D2<=0,"N/A",(C2/D2))  to Row 43: =IF(D43<=0,"N/A",(C43/D43))

Column H :  =IF(D2>0,SUM(F2:G2),0)  to Row 43: =SUM(F43:G43)

Column I : =IF(H2>0,(H2/D2),"N/A") to Row 43: =IF(H43>0,(H43/D43),"N/A")

A	B	C	D	E	F	G	H	I	Date
	32	29	61	47.5%	6.26	133.91	140.17	2.298	APRIL 2021
	72	0	72	0.0%	0.99	84.16	85.15	1.183	APRIL 2021
	66	223	289	77.2%	30.09	287.41	317.50	1.099	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	202	93	295	31.5%	29.77	848.15	877.92	2.976	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	819	10,924	11,743	93.0%	1,235.69	6,200.91	7,436.60	0.633	APRIL 2021
	710	1,886	2,596	72.7%	269.70	2,420.28	2,689.98	1.036	APRIL 2021
	123	322	445	72.4%	46.25	592.31	638.56	1.435	APRIL 2021
	3,559	11,015	14,574	75.6%	1,515.98	12,351.17	13,867.15	0.951	APRIL 2021
	83	1,127	1,210	93.1%	127.32	870.46	997.78	0.825	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	583	1,040	1,623	64.1%	167.48	4,685.92	4,853.40	2.990	APRIL 2021
	1,304	182	1,486	12.2%	148.00	1,246.17	1,394.17	0.938	APRIL 2021
	6,938	11,374	18,312	62.1%	1,887.77	16,658.17	18,545.94	1.013	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	4,717	24,930	29,647	84.1%	3,104.27	31,423.83	34,528.10	1.165	APRIL 2021
	2,454	15,390	17,844	86.2%	1,869.25	18,757.68	20,626.93	1.156	APRIL 2021
	3,455	29,402	32,857	89.5%	3,449.10	29,279.03	32,728.13	0.996	APRIL 2021
	1,773	10,517	12,290	85.6%	1,286.71	9,997.20	11,283.91	0.918	APRIL 2021
	1,723	4,842	6,565	73.8%	682.10	7,800.38	8,482.48	1.292	APRIL 2021
	919	1,488	2,407	61.8%	248.04	5,154.51	5,402.55	2.245	APRIL 2021
	7,215	21,667	28,882	75.0%	3,009.31	38,899.19	41,908.50	1.451	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	719	1,076	1,795	59.9%	184.70	1,018.91	1,203.61	0.671	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	101	176	277	63.5%	28.58	301.85	330.43	1.193	APRIL 2021
	3,307	3,635	6,942	52.4%	711.91	8,714.71	9,426.62	1.358	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	1,743	888	2,631	33.8%	265.98	4,111.80	4,377.78	1.664	APRIL 2021
	10		10	0.0%	0.99	60.97	61.96	6.196	APRIL 2021
	374	143	517	27.7%	52.05	1,619.56	1,671.61	3.233	APRIL 2021
	0	0	0	N/A	0.00	0.00	0.00	N/A	APRIL 2021
	10	43	53	81.1%	5.53	26.06	31.59	0.596	APRIL 2021
	8	45	53	84.9%	5.55	28.62	34.17	0.645	APRIL 2021
Totals:	43,019	152,457	195,476	78.0%	$       20,369.37	$   203,573.32	$   223,942.69	1.146

[VahidDM]

Hi @sdhn 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/