"OTHERS" row keeps showing blank for a specific measure

[leolapa_br]

I have this simple accounts payable data model:

On this model I created a 'Vendors_With_Others' table, which is basically a list of all vendors that show up on the 'Payables' table with an extra row with "OTHERS" for purposes of the report that I want to build.

The 'Base_Date' table that sits outside the highlighted area is basically a one-cell table that dinamically pulls the date the report is based on as per input on a PQ parameter by the user who is supposed to update it. This date is the reference which each row gets its number of 'Days' outstanding calculated (either number of days vs. each respective future due date or number of overdue days for those rows whose respective due dates are in the past).

And also sitting outside the core model are 'Status'/'ABC Curve Cutoff' which were generated via field/numeric range parameters that were created to provide visual aids to the end user.

So, my end goal was to create a table visual that displays only those vendors whose total amounts (due or past due as set by the 'Status' slicer) are equal to or greater than the amount set by the ABC Curve Cutoff slicer placed next to it, and the remainder are all summed up and shown under an "OTHERS" row, which I sucessfully built as per the screenshot that follows:

Then I went on an additional step, which was to calculate a weighted average of 'Days' for each vendor, the weight being the total 'Amount' for each respective vendor.

First I did manage to successfully put it together, but when I replicated the same calculation on Excel I realized the measure I had created is performing the wrong calculation for the "OTHERS" row.

So I rewrote the code which now successfully calculate the weighted average days outstanding for the "OTHERS" row (57 days), as per the screenshot below:

However, when I place this specific measure on the table visual it keeps showing blank for it:

Here is the code for this 'Days (correct)' measure:

Days (correct) = 
VAR StatusSlicer = SELECTEDVALUE('Status'[Status])
VAR BaseDate = SELECTEDVALUE(Base_Date[Base date])
VAR ABCCurveCutoff = [ABC Curve Cutoff Value]
VAR OthersSelec = SELECTEDVALUE(Vendors_With_Others[Vendor name]) = "OTHERS"
VAR Payables_Days = -- Temp Payables table with an additional column that calculate days outstanding for each row.
	ADDCOLUMNS(
		Payables,
		"@Days", IF(
			MAX(Base_Date[Base date]) > Payables[Due date],
			INT(MAX(Base_Date[Base date]) - Payables[Due date]),
			INT(Payables[Due date] - MAX(Base_Date[Base date]))
		)
	)
VAR Payables_Tbl_DaysxAmt = -- Temp table that filters out those rows that do not belong to the 'Status' slicer (either 'Past due or 'Due').
	FILTER(
		SUMMARIZE(
			Payables_Days,
			Vendors_With_Others[Vendor name],
			Payables[Document],
			Payables[Due date],
			Payables[Amount],
			[@Days]
		),
		SWITCH(
			TRUE(),
			StatusSlicer = "Past due", Payables[Due date] < BaseDate,
			StatusSlicer = "Due", Payables[Due date] >= BaseDate,
			0
		)
	)
VAR Payables_Tbl_DaysxAmt_Wgt = -- Temp table that adds to the previous VAR table a column that calculates the weight of the $ amount for each individual row vs. the total for each respective vendor, filtered by the 'Status' slicer.
	ADDCOLUMNS(
		Payables_Tbl_DaysxAmt,
		"@Weight", DIVIDE(
			Payables[Amount],
			CALCULATE(
				SUM(Payables[Amount]),
				ALLEXCEPT(
					Payables,
					Vendors_With_Others[Vendor name]
				),
				SWITCH(
					TRUE(),
					StatusSlicer = "Past due", Payables[Due date] < BaseDate,
					StatusSlicer = "Due", Payables[Due date] >= BaseDate,
					0
				)
			),
			0
		)
	)
VAR Payables_Tbl_DaysxAmt_Wgt_DaysWgt = -- Temp table that adds to the previous VAR table a column that applies the weight to each respective 'Days' outstanding for each row.
	ADDCOLUMNS(
		Payables_Tbl_DaysxAmt_Wgt,
		"@DaysWeight", [@Weight] * [@Days]
	)
VAR Payables_Tbl_Amt_DaysByVendor = -- Temp table that summarizes the previous VAR table by vendor and adds a column with the total $ amount for each respective vendor and another column with the total weighed 'Days' outstanding for each respective vendor.
	ADDCOLUMNS(
		SUMMARIZE(
			Payables_Tbl_DaysxAmt_Wgt_DaysWgt,
			[Vendor name]
		),
		"@Amount", VAR Forn = [Vendor name]
			RETURN
				SUMX(
					FILTER(
						Payables_Tbl_DaysxAmt_Wgt_DaysWgt,
						[Vendor name] = Forn
					),
					[Amount]
				),
		"@DaysByVendor", VAR Forn = [Vendor name]
			RETURN
				ROUND(
					SUMX(
						FILTER(
							Payables_Tbl_DaysxAmt_Wgt_DaysWgt,
							[Vendor name] = Forn
						),
						[@DaysWeight]
					),
					0
				)
	)
VAR QuantVendorsBelowABCCurveCutoff = -- Quantity of vendors whose total $ amount are smaller than the ABC curve cutoff value set by the slicer (which will be considered as "OTHERS").
	COUNTROWS(
		FILTER(
			Payables_Tbl_Amt_DaysByVendor,
			[@Amount] < ABCCurveCutoff
		)
	)
VAR VendorsBelowABCCurveCutoff_Amt_Days = -- Temp table that lists only the bottom 'N' vendors by $ amount, 'N' being the quantity calculated by the previous VAR constant.
	TOPN(
		QuantVendorsBelowABCCurveCutoff,
		Payables_Tbl_Amt_DaysByVendor,
		[@Amount],
		ASC
	)
VAR VendorsBelowABCCurveCutoff_Amt_Days_AmtxDays_Wgt = -- Temp table with only the vendors to be considered under the "OTHERS" row and that adds a column that calculates the $ amount weight of each vendor over the grand total.
	ADDCOLUMNS(
		VendorsBelowABCCurveCutoff_Amt_Days,
		"@WgtBelowABCCurveCutoff", DIVIDE(
			[@Amount],
			SUMX(
				VendorsBelowABCCurveCutoff_Amt_Days,
				[@Amount]
			),
			0
		)
	)
VAR VendorsBelowABCCurveCutoff_Amt_Days_AmtxDays_Wgt_DaysWgt = -- Temp table that adds to the previous VAR table a column that calculates the final weighed 'Days' outstanding for each vendor to be considered under the "OTHERS" row.
	ADDCOLUMNS(
		VendorsBelowABCCurveCutoff_Amt_Days_AmtxDays_Wgt,
		"@DaysWgtBelowABCCurveCutoff", [@DaysByVendor] * [@WgtBelowABCCurveCutoff]
	)
VAR Days_VendorsBelowABCCurveCutoff = -- Sum of all weighed 'Days' outstanding of all vendors to be considered under the "OTHERS" row.
	ROUND(
		SUMX(
			VendorsBelowABCCurveCutoff_Amt_Days_AmtxDays_Wgt_DaysWgt,
			[@DaysWgtBelowABCCurveCutoff]
		),
		0
	)
RETURN --  If the row on the table visual is "OTHERS" then return the sum of all weighed 'Days' outstanding of all vendors to be considered under the "OTHERS" row, otherwise return the 'Days' outstanding for each respective vendor.
	IF(
		OthersSelec,
		Days_VendorsBelowABCCurveCutoff,
		[Days Calc]
	)

What am I missing here? Any help is greatly appreciated.

Since I couldn't find a way to share the actual .pbix file, here is the link to download it in case someone wants to take a stab at this...

Payables Report.pbix 

[lbendlin · Accepted solution]

This article may get you over the hump:  Filtering the top products alongside the other products in Power BI - SQLBI

[leolapa_br]

Only now I noticed that there is a section of the article that deals with non-additive measure, which is exactly the part I needed to get me across the finish line. Thanks!

[leolapa_br]

Thanks @lbendlin, that's exactly the article I used to attend the requirements of my report, it however does not address the particular issue I'm experiencing.

Understandably so, since it covers calculation of "OTHERS" row for the $ amount, which is basically the difference between the grand total and the total of all the rows displayed in the report.

For 'Days' the calculation requires much more intermediary steps as it deals with weighted average calculation. And coming up with a plug difference won't cut it: it is necessary to calculate the average 'Days' outstanding for all the rows that are not being displayed in the report. Which I was able to do it, I somehow just can't get that number to show up in the visual under the "OTHERS" row.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[leolapa_br]

Hi @lbendlin,

At the very end of my original post I provide a OneDrive link with the .pbix file of the sample data I've been using to try to solve this.

In it you'll notice that are 4 versions of the "correct" measure (1.1, 1.2, 2.1 and 2.2) as all of them do provide the correct weighted average 'Days' outstanding figure for the "OTHERS" row, except none of them displays such figure on a table visual.

Also, I believe my original post although lenghty does follow the guidelines to effectively tell the story and goal I want to achieve.