Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello All,
Can anybody please provide me DAX formula to apply only in Measure and not in Add Column for the below requirement?
Hello Team,
I need DAX formula to get as below excel calculation
On D2 cell given as =C2
On D3 cell given as =D2+C3 and it should continue till last row
On E2 cell given as =IF(AND(D2<0.9,D3>=0.9),A2+(A3-A2)*(0.9-D2)/(D3-D2),0) and it should continue till last row.
Row Labels | Count of Sotat | Count of Sotat2 | FR_Per | Fill Rate 90 |
0 | 8965 | 73.96% | 73.96% | 0 |
1 | 1035 | 8.54% | 82.49% | 0 |
2 | 860 | 7.09% | 89.59% | 2.076497696 |
3 | 651 | 5.37% | 94.96% | 0 |
4 | 129 | 1.06% | 96.02% | 0 |
5 | 77 | 0.64% | 96.66% | 0 |
6 | 62 | 0.51% | 97.17% | 0 |
7 | 115 | 0.95% | 98.12% | 0 |
8 | 40 | 0.33% | 98.45% | 0 |
9 | 34 | 0.28% | 98.73% | 0 |
10 | 46 | 0.38% | 99.11% | 0 |
11 | 11 | 0.09% | 99.20% | 0 |
12 | 14 | 0.12% | 99.32% | 0 |
13 | 22 | 0.18% | 99.50% | 0 |
14 | 23 | 0.19% | 99.69% | 0 |
15 | 11 | 0.09% | 99.78% | 0 |
20 | 3 | 0.02% | 99.80% | 0 |
21 | 13 | 0.11% | 99.91% | 0 |
29 | 4 | 0.03% | 99.94% | 0 |
30 | 1 | 0.01% | 99.95% | 0 |
33 | 1 | 0.01% | 99.96% | 0 |
41 | 3 | 0.02% | 99.98% | 0 |
52 | 2 | 0.02% | 100.00% | 0 |
Thank you in advance
Regards,
AK
Solved! Go to Solution.
Hi @krishak77 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Create a calculated column as below to get it:
Fill Rate 90 =
VAR _nextlabel =
CALCULATE (
MIN ( 'Table'[Row Labels] ),
FILTER ( 'Table', 'Table'[Row Labels] > EARLIER ( 'Table'[Row Labels] ) )
)
VAR _nextfrper =
CALCULATE (
MAX ( 'Table'[FR_Per] ),
FILTER ( 'Table', 'Table'[Row Labels] = _nextlabel )
)
RETURN
IF (
[FR_Per] < 0.9
&& _nextfrper >= 0.9,
'Table'[Row Labels] + ( _nextlabel - 'Table'[Row Labels] ) * ( 0.9 - 'Table'[FR_Per] ) / ( _nextfrper - 'Table'[FR_Per] ),
0
)
Best Regards
measures are working with aggregated data. Your calculations clearly work on row level so they need calculated columns or (more likely) Power Query code to work.
Hi @lbendlin ,
Thanks for your response.
The about table is pivot table from large data of Excel, and I need this to calculate in Power Bi only for showing above table by using Matrix chart in Power Bi.
So could you please help to provide Dax formula to applied in measure?
Thank you in advance.
Hi @krishak77 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Create a calculated column as below to get it:
Fill Rate 90 =
VAR _nextlabel =
CALCULATE (
MIN ( 'Table'[Row Labels] ),
FILTER ( 'Table', 'Table'[Row Labels] > EARLIER ( 'Table'[Row Labels] ) )
)
VAR _nextfrper =
CALCULATE (
MAX ( 'Table'[FR_Per] ),
FILTER ( 'Table', 'Table'[Row Labels] = _nextlabel )
)
RETURN
IF (
[FR_Per] < 0.9
&& _nextfrper >= 0.9,
'Table'[Row Labels] + ( _nextlabel - 'Table'[Row Labels] ) * ( 0.9 - 'Table'[FR_Per] ) / ( _nextfrper - 'Table'[FR_Per] ),
0
)
Best Regards
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |