Calculating Percentage of Total Across Multiple Days

[DJKole]

Hello,

I have a daily AR report that refreshes in PBI.  There's two tabs, one to select a specific day, and one that looks at the past x days in a Matrix to see trends.  On both tabs I need to be able to review a percentage of multiple columns (i.e. Total Past Due 60+/Total Balance, Total Past Due 120+/Total Balance, etc.).  I created two measures to accomplish this, and it is working successfully on the specific day tab.

Measure 1 = 

%181 Total = CALCULATE(SUM(Query1[181]),ALLSELECTED(Query1))

Measure 2 = 

180+ Percent = DIVIDE(Query1[%181 Total],Query1[% Total])

I created a measure for each bucket that I'm calculating.  Whatever filter I choose on the specific day tab it is calculating correctly and working fine.

However, I cannot get this to work correctly on the day trend tab, because multiple days are being considered here.  I'm guessing this is due to the 'All Selected' piece in my formula above, but how I can adjust these so both tabs will work?  Or do I need to have different measures for each of these tabs.

Ultimately, I'm trying to get either a Matrix or Table that looks like this:

[v-echaithra]

Hi @DJKole ,

Thank you @rohit1991  for your inputs.

I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue with the provided solutions. Please let us know if you need any further assistance.

Thank you.

[DJKole]

Hi Rohit,

Thanks for the response.  I prefer Option B as I only want to show the % for the past due buckets.  One issue with your measures, is for 60/120 buckets I need to add additional columns of data.  For example, there's a column for 61-90, another for 91-120, 121-180, and 180+.  So for the 60+% it would need to be column 61-90 + 91-120 + 121-180 + 180+ / Total Balance as that's the true amount of what's 60+, since it encompasses those other buckets as well.

Nevertheless, your expression should still have worked for the 180+% since that's just a single bucket where we are dividing, yet something did not work.  I added your measures above (noting Long_AR = Query 1 the name of my data):

Amount = SUM (Query1[Total Balance]) - Total Balance is the total amount owed by the customer

Total Bal = CALCULATE ( [Amount], REMOVEFILTERS ( Query1[181] ) ) - The singular bucket in this case

% 180+ =

DIVIDE (

   CALCULATE ( [Amount], KEEPFILTERS (Query1[181] = "180+" ) ),

   [Total Bal]

)

I then formatted the Matrix to your specs and it results in the same percentage for each day (which is the total % of all days combined):

[v-echaithra]

Hi @DJKole ,

Thank you for your update.
Could you please share a sample dataset that reflects your scenario? This will help us accurately reproduce your requirement and provide the most possible solution.

Warm Regards,
Chaithra E.

[DJKole]

Hi @v-echaithra below is as a sample of the data set.  The last column will update each day with the current date as the new data set is refreshed and brough into PBI

[rohit1991 · Accepted solution]

Hi @DJKole 

Your table is in a wide layout (separate columns for 61-90, 91-120, 121-180, 180+). My earlier measure assumed a single Bucket column, so that’s why % 180+ was coming out the same for every date.

You can fix this in either of two ways:

Option A (Recommended): Unpivot to a Bucket column

Power Query >> select the 4 aging columns >> Unpivot Columns >> you’ll get:

• Bucket (text: “61-90”, “91-120”, “121-180”, “180+”)
• Amount (numeric)

Then measures:

Total Balance =
SUM ( Query1[Total_Balance] )

Bucket Amount =
SUM ( Query1[Amount] )

Single bucket

% 180+ =
DIVIDE (
   CALCULATE ( [Bucket Amount], Query1[Bucket] = "180+" )
   [Total Balance]
)

60+ is a rollup of all older buckets

Amount 60+ =
CALCULATE ( [Bucket Amount], Query1[Bucket] IN { "61-90","91-120","121-180","180+" } )

% 60+ =
DIVIDE ( [Amount 60+], [Total Balance] )

This respects the Report_Date on your visual automatically. (If you use a separate Date table, relate Date[Date] >> Query1[Report_Date] and put Date[Date] on Columns.)

Option B (Keep data as-is; no unpivot)

Use the physical columns directly:

Total Balance =
SUM ( Query1[Total_Balance] )

Amount 180+ =
SUM ( Query1[180+] )

% 180+ =
DIVIDE ( [Amount 180+], [Total Balance] )

Amount 60+ =
SUM ( Query1[61-90] )
   + SUM ( Query1[91-120] )
   + SUM ( Query1[121-180] )
   + SUM ( Query1[180+] )

% 60+ =
DIVIDE ( [Amount 60+], [Total Balance] )

Important: Don’t wrap the denominator in ALL()/REMOVEFILTERS() for Date; otherwise you’ll get the same % for every day. Just use plain SUM(...) so the Report_Date filter on the matrix is honored.

[rohit1991]

Hi @DJKole 

You don’t need two completely different sets of measures for “single-day” vs “multi-day” tabs  the key is in how you want to display the buckets.

Option A : Using one measure with the Bucket field

If you’re happy to show your buckets dynamically (as rows coming from the data itself), then a single measure is enough:

Amount = SUM ( AR_Long[Amount] )

Total Balance = CALCULATE ( [Amount], REMOVEFILTERS ( AR_Long[Bucket] ) )

% of Total = DIVIDE ( [Amount], [Total Balance] )

Matrix setup

• Rows >> Bucket
• Columns >> Date
• Values >> % of Total

This gives you the top matrix in your screenshot: each bucket shows its % of that date’s total. Works for one date (via slicer) or many dates (trend view).

Option B : Fixed rows with separate measures

If you want rows fixed as “% Current, % 60+, % 120+, % 180+” (instead of pulling them from the Bucket column), then create four individual measures:

% Current =
DIVIDE (
   CALCULATE ( [Amount], KEEPFILTERS ( AR_Long[Bucket] = "Current" ) ),
   [Total Balance]
)

% 60+ =
DIVIDE (
   CALCULATE ( [Amount], KEEPFILTERS ( AR_Long[Bucket] = "60+" ) ),
   [Total Balance]
)

% 120+ =
DIVIDE (
   CALCULATE ( [Amount], KEEPFILTERS ( AR_Long[Bucket] = "120+" ) ),
   [Total Balance]
)

% 180+ =
DIVIDE (
   CALCULATE ( [Amount], KEEPFILTERS ( AR_Long[Bucket] = "180+" ) ),
   [Total Balance]
)

Matrix setup

• Columns >> Date
• Values >> % Current, % 60+, % 120+, % 180+
• Format >> Show values on rows = On

This gives you the second matrix in your screenshot, with fixed rows for each bucket.

That’s what makes it work for both single-day and multi-day views: the Date context remains, only the Bucket filter is removed.

So

• Use % of Total if you want dynamic buckets.
• Use individual % measures if you want fixed rows.