Rank YTD Total over 2 added Measures

Hi @chris.turnbull, please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

Hi Brian,

That is what I want and what I have but I want an added extra of being to be able to sum the Monthly Composite Scores to have a Yearly or To Date Composite Score depending on the Date Selection.

Cheers

Chris

@chris.turnbull,

That should be pretty straightforward to implement - basically just changing the ALL functions in the component and composite ranking measures to ALLSELECTED to make the calculations responsive to the selections in your date slicer.

If you really want to polish this up and provide a range of pre-specified period options in your date slicer, you could implement this technique. To see it in action, check out @greg’s entry for Data Challenge #5.

image

  • Brian

Hi this is the model and the measures.

There are 2 Grids, the Top Grid is for April-20 and ranks 4 Categories and then gives an Overall Ranking. The bottom grid gives the Overall Ranking over time. I want to add an extra column the to the top grid that gives the Overall Ranking points summed Monthly for YTD.

I have also noticed the Issue with the Totalled Ranking Points in the bottom grid and I am having trouble understanding what is going on.

You will also notice from the measures that the Financial Year Starts on May 1st.

Hope this makes sense and thank you for your help.

Chris

EDNA RankX Model.pbix (4.0 MB)

Hi @chris.turnbull. I’m starting on your post, but am presented with the “Apply Changes” bar as soon as I open your PBIX … can you please Apply the Changes then repost the PBIX? Thanks, Greg

Also @chris.turnbull, I think I’m clear on what you’re looking for, but could you please as well mock-up in Excel exactly the outcome you’re looking for while you’re at it? I don’t want to unnecessarily “spin-your-wheels” if I don’t have to. Thanks, Greg

Hi Greg,

Thanks for looking at this I simply want a measure that gives me the Cumulative Overall Ranking Figure.

eg. If I am showing the Top Grid in the pbix for July -20. I would want to sum the Ranking Points for May, June and July to give me the Overall Total for my Finacial YTD which starts on May 1st.

So if Store 1 had 5 points for May, 2 for June and 3 for July the YTD Total would be 10.

EDNA RankX Model.pbix (4.0 MB)

It would be great if I was able to see the Row and Column Totals and the Scores cumulatively as in the Excel sheet below.

Greg EDNA.xlsx (9.6 KB)

Thanks

Chris

Hi @chris.turnbull. Are you looking for the cumulative totals from the beginning of time or should they reset to zero at the start or each fiscal year (i.e., omit the Apr-20 numbers in your example)? (Not there yet, but caught this during my initial setup…)

Here’s my markup of the Excel with a possible cumulative matrix: is this what you’re looking for?

Greg

Hi Greg,

Yes Cumulative for each FY starting in May.

Thanks

Chris

Hi @chris.turnbull.

Well, I’ve made some progress on your issue but am getting stuck in an virtual table-evaluation context hole and am “spinning-my-wheels”. The solution isn’t dropping-out for me, and I’ve attached my work-in-progress so hopefully one of the other forum members can see an easy fix.

Cumulative Ranking YTD = 
VAR _CurrentFiscalYear = SELECTEDVALUE( 'Calendar'[Fiscal Year] )
VAR _CurrentFiscalPeriod = SELECTEDVALUE( 'Calendar'[Fiscal Period] )
VAR _vTable = 
ADDCOLUMNS(
CALCULATETABLE(
    CROSSJOIN( 
        VALUES( Branch[Group Restructure] ), 
        VALUES( 'Calendar'[Month Year] ) ),                     -- this is the line that is causing the issue, in that its filtering for the current [Month Year]
        --ALLSELECTED( 'Calendar'[Month Year] ) ),              -- gives the same values as VALUES
        --ALL( 'Calendar'[Month Year] ) ),                      -- not correct
        --ALLEXCEPT( 'Calendar', 'Calendar'[Month Year] ) ),    -- not correct
	FILTER( 'Calendar', 
	    'Calendar'[Fiscal Year] = _CurrentFiscalYear &&
		'Calendar'[Fiscal Period] <= _CurrentFiscalPeriod
		) 
	),
	"@Ranking", [Overall Ranking]
	)
VAR _Result = 
SUMX(
    _vTable,
    [@Ranking]
)

RETURN
    _Result

I’ll keep trying and will post again if I find anything.

Greg
eDNA Forum - RANKX YTD over 2 Added Measures V1.pbix (4.0 MB)

Hi Greg, it is a bit if tricky one but thanks for your help.

@Greg I think you should use ALL ( ‘Calendar’ ) that way CROSSJOIN will return a combination of visible Branch[Group Restructure] and all of the Calendar[Month Year]

@chris.turnbull I almost thought I won’t be able to sleep tonight, in case I didn’t solve this.

Measure =
VAR MaxGroupRestructure =
    MAX ( Branch[Group Restructure] )
VAR Result =
    SUMX (
        FILTER (
            ADDCOLUMNS ( ALL ( Branch[Group Restructure] ), "Rank", [Overall Ranking] ),
            Branch[Group Restructure] <= MaxGroupRestructure
        ),
        [Rank]
    )
RETURN
    Result
2 Likes

Hi @AntrikshSharma. Thanks for the solution. Could you please attach your PBIX? (as I wasn’t able to get your formula to work for me; its providing a cumulative total across stores rather than across months, whereas your screenshot looks correct …) Thanks, Greg

Hi @Greg refer to this: eDNA Forum - RANKX YTD over 2 Added Measures V1.pbix (4.0 MB)

Awesome @AntrikshSharma … thanks for the review/solution. Grr! :rage: Doh! I knew it was something small that was eluding me. @chris.turnbull, here’s the (now) working measure:

Cumulative Ranking YTD = 
VAR _CurrentFiscalYear = SELECTEDVALUE( 'Calendar'[Fiscal Year] )
VAR _CurrentFiscalPeriod = SELECTEDVALUE( 'Calendar'[Fiscal Period] )
VAR _vTable = 
ADDCOLUMNS(
CALCULATETABLE(
    CROSSJOIN( 
        VALUES( Branch[Group Restructure] ), 
        VALUES( 'Calendar'[Month Year] ) ),
	FILTER( ALL ( 'Calendar' ), 
	    'Calendar'[Fiscal Year] = _CurrentFiscalYear &&
		'Calendar'[Fiscal Period] <= _CurrentFiscalPeriod
		) 
	),
	"@Ranking", [Overall Ranking]
	)
VAR _Result = 
SUMX(
    _vTable,
    [@Ranking]
)

RETURN
    _Result

Hope it helps.

Greg

1 Like

Hi @chris.turnbull, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi Antrik,

Sorry for the delay in replying I didn’t get an email to let me know that you had replied.

I can’t thank you enough, I lost hours of sleep over this one. It was the last measure in a big project that I am demonstrating to the big cheeses on Friday morning

This forum never ceases to amaze me.

Thank I can’t convey his much I appreciate your time.

:grinning::+1:

2 Likes

Greg thank you so much for your help and time. Hopefully I can do the same for you sometime. :grinning::+1:

1 Like

You’re welcome! :smiley: Rock your meeting!

2 Likes