Calculate % of total within selected period

[jaco1951]

Hi

I have searched up and down to find a working solution, but no luck so far.

I have a bunch of loans that I put a filter on, removing those with company 103. I also have a filter that says date = max(date) because I select on month level.

I want to create a % of total for the remaining loans, based on the sum without compay 103.

How can I do this??

I've tried something like this:

debt in % = CALCULATE(SUMX(factTable;factTable[Debt Total])) / [Measure Total Debt]

Where [Measure Total Debt] =
CALCULATE(SUMX(factTable;factTable[Debt Total]);
FILTER(dimSecurity;dimSecurity[Company] <> "103");
FILTER(cal_Securities;cal_Securities[date] = MAX(cal_Securities[date]))

But once I make a selection in any field that Measure Total Debt starts to change... 

Anyone who can explain in details how I solve this?

Br Espen

[edhans]

Try adding one more filter to calculate:

ALL([Company])

I am not 100% positive that filtering "<> 103" will work with the ALL (i.e. will ALL force that filter to be ignored?), but give it a shot first. I'd need to play with some real data to test this.

[edhans]

Wasn't quite that simple. This worked for me though:

Total Measured Debt = 
CALCULATE(
    [Total Debt],
    FILTER(
        ALL(Debt[Company]),
        Debt[Company]<>"103")
)

You don't need to filter the date at all as that will automatically fitler based on the filter context of your table/matrix.

BUt it adds up all debt for all companies excluding co 103.

[jaco1951]

Hi edhans

Thank you for your input, I have tested some stuff now, and this formula works except from one thing, I'm not able to filter away the companies that I dont want to be in the total.

If I add a filter for company the formula just end up showing 100% on all rows...

I had to add the date filter, as i only select month, and not the date, there are 31 records for the month. But it works fine, all for the company stuff, the grand total is currently 91%, not 100%, which means that my page filter exclude the companies, but the forumla still add them as the grand total.

loan% = 
DIVIDE(SUM(factTable[Loan Balance End USD - Reported Value]);
CALCULATE(SUM(factTable[Loan Balance End USD - Reported Value]);
FILTER(cal_Securites;cal_Securites[Date] = MAX(cal_Securites[Date]));
ALLEXCEPT(factTable;factTable[%Sec_CFP]));0)

thanks 

espen

[edhans]

Hi @jaco1951 

I didn't skip the SUM function. [Total Debt] is a measure that is =SUM[Debt[Debt]) - I wasn't at all clear about that. Rule of thumb though when reading articles, as long as you aren't looking at calculated columns, a measure is always [Measure] without a table qualifier and a column is always TableName[ColumnName] with the table qualifier.

To help further, I would need to see a PBIX file with some data in it to see exactly where it is going wrong. Too many variables for me to keep in my head and trouble shoot - like is there a data table, which date is in your visual (date table or data table), where is the Month number coming from, how are you selecting, etc.

[jaco1951 · Accepted solution]

Hi @edhans

I found the solution, not 100% if I understand the logic, but it worked when I added the filter for companies without using the filter function:

total reported debt = 
CALCULATE(SUM(factTable[Loan Balance End USD - Reported Value]);
	dimSecurity[CompNo.] <> "103"; 
		dimSecurity[CompNo.] <> "104";
			dimSecurity[CompNo.] <> BLANK();
FILTER(cal_Securites;cal_Securites[Date] = MAX(cal_Securites[Date]));
ALLEXCEPT(factTable;factTable[%Sec_CFP]))

[jaco1951]

Hi @edhans

There seems to be some delay on these messages, because I thought you hadn't seen my post, and therefore I edited it.

As you can see from the edited post I found a DAX formula that is very close to work, except from the fact that I cannot filter away compaines. If I try to add that filter, it goes back to show the value for each row, not the total anymore.

And thank you for the info about the measure / column thing, I didn't think about that. Nice to know.

Br Espen