Rank YTD Total over 2 added Measures

[chris.turnbull]

Hi All,

I am ranking by 2 Measures - Average Invoice Value Group Ranking and Average Net Sales Group Ranking and Summing them that gives me a monthly Ranking Total for each Group Restructure.

All that is fine but I am struggling to add those Monthly Ranking Totals together to give me a YTD Overall Ranking Total for each Group.

Simple 3 Table Model with FactSales, Calendar and Location. With FactSales relating to Location via a LocationID.

Any help would be welcome.

Average Invoice Value Group Ranking =
IF (
HASONEVALUE( Branch[Group Restructure] ),
RANKX (
ALL ( Branch[Group Restructure] ),
[Average Invoice Value]
),
BLANK ()
)

---

Average Net Sales Group Ranking =
IF (
HASONEVALUE( Branch[Group Restructure] ),
RANKX (
ALL ( Branch[Group Restructure] ),
[Average Net Sales Per Branch]
),
BLANK ()
)

Total Group Ranking = [Average Net Sales Group Ranking] + [Average Invoice Value Group Ranking]

[chris.turnbull]

Hi All,

So I have found this https://blog.enterprisedna.co/working-with-virtual-in-memory-tables-in-power-bi-using-dax/ - Sam you are amazing and it gives me the monthly Overall Rank so how would I then get the YTD Overall Rank which would mean adding all the monthly ranking Points. My Financial Year starts on May 1st.

This is my version

Overall Ranking =
VAR AverageInvoiceValueGroupRanking = RANKX (ALL ( Branch[Group Restructure] ), [Average Invoice Value])
VAR AverageNetSalesGroupRanking = RANKX (ALL ( Branch[Group Restructure] ), [Average Net Sales Per Branch] )
VAR AverageInvoicesPerBranchGroupRanking = RANKX ( ALL ( Branch[Group Restructure] ), [Average Invoices Per Branch])
VAR NetSalesLYDiffRanking = RANKX (ALL ( Branch[Group Restructure] ), [Slicer Selection Net Sales LY Diff %])
RETURN
SUMX(
SUMMARIZE(
Branch,
Branch[Group Restructure],
“Invoice Value Rank”, AverageInvoiceValueGroupRanking,
“Net Sales Rank”, AverageNetSalesGroupRanking,
“Invoices Per Branch Rank”, AverageInvoicesPerBranchGroupRanking,
“Net Sales LY Diff Rank”, NetSalesLYDiffRanking ),

[Invoice Value Rank] + [Net Sales Rank] + [Invoices Per Branch Rank] + [Net Sales LY Diff Rank])

Thanks

Chris

[Greg]

Hi @chris.turnbull.

To help us pursue further visualize your issue, could you please provide as many as you can of:

• Your work-in-progress PBIX file, using sanitized data if necessary
  • here’s @BrianJ’s YouTube video on how to anonymizing data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• Your dataset as an Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Greg

[BrianJ]

@chris.turnbull,

One other thing you may find helpful is my Data Challenge #4 entry. On the report page screenshot below, I created a composite ranking index approach composed of five other rankings. Explanation in the thread below, along with my PBIX where you can go through all the relevant measures, etc.

Challenge 4- Delivery App Data Entry from Brian

@EnterpriseDNA, BACKGROUND: My approach to these challenges balances four different perspectives: Consultant - I try to role play these from the vantage point of a consultant, focused on answering the questions at hand, while also trying to find ways to provide some extra value to the client. I also pretend not to know info that would be unknown to the consultant (e.g., that the data for these are generated randomly) Client - having spent years as a manager overseeing teams of analysts, I…

I hope this is helpful to you.

• Brian

[EnterpriseDNA]

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!

https://analysthub.enterprisedna.co/dax-clean-up

[chris.turnbull]

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

[BrianJ]

@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.

• Brian

[chris.turnbull]

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)

[Greg]

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

[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

[chris.turnbull]

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

[Greg]

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

[chris.turnbull]

Hi Greg,

Yes Cumulative for each FY starting in May.

Thanks

Chris

[Greg]

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)

[chris.turnbull]

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

[AntrikshSharma]

@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]

[AntrikshSharma]

@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

[Greg]

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

[AntrikshSharma]

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

[Greg]

Awesome @AntrikshSharma … thanks for the review/solution. Grr! 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