Forum Discussion
Show the Value from a Cell: using Dynamic Index || Count Dax Formula?
Hello All,
I have a table of power market which contains a column of Daily Return called "LN" (Sort Descending).
I have written a measure which has given me the specific number of the row containing the value I need to know on column LN. This number can change because of the period chosen.
Scenario VAR = ROUNDUP((0.95)*COUNT('GFI German Base power M1'[LN]),0)
Here for instance I want from the Report View find the row 86 of the Column LN.
I tried to create a system of Index but some number of the order is missing when I move the slicer of the period from the report view.
I tried these formulas but no luck .
Ln order = RANKX( ALL('GFI German Base power M1'), 'GFI German Base power M1'[LN], , ASC, Dense)IndexColumn = COUNTX(FILTER('GFI German Base power M1', 'GFI German Base power M1'[LN] <= EARLIER('GFI German Base power M1'[LN])), 'GFI German Base power M1'[LN])
Can any one please suggest me with a solution.
Best,
Romain.A
I already tried the formulas that you proposed and it didn't worked. I finally find out a solution by my own.
VAR_95 = CALCULATE(SUM('GFI German Base power M1'[LN]),TOPN(([Scenario VAR]),'GFI German Base power M1',[LN], ASC))-CALCULATE(SUM('GFI German Base power M1'[LN]),TOPN(([Scenario VAR]-1),'GFI German Base power M1',[LN], ASC))
2 Replies
- amitchandak
Super User
Falaerm , have you check if new rownumber or rank function can help
New DAX Function: Rownumber- https://www.youtube.com/watch?v=yS9-IQjUDwg&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1
Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo
or window
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07fI already tried the formulas that you proposed and it didn't worked. I finally find out a solution by my own.
VAR_95 = CALCULATE(SUM('GFI German Base power M1'[LN]),TOPN(([Scenario VAR]),'GFI German Base power M1',[LN], ASC))-CALCULATE(SUM('GFI German Base power M1'[LN]),TOPN(([Scenario VAR]-1),'GFI German Base power M1',[LN], ASC))
