Forum Discussion
Responsive ResidentDAX Calc.
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.
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+_G43Column 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.
3 Replies
- v-robertq-msft
Community Support
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+_G43Column 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.
- VahidDM
Super User
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/ 
sdhnColumn 😧 =(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
