Filter the total count for the month then divide by each Category of that month

[deng2431]

Hi PowerBI Community,

Being at this requirement for days but still couldnt figure it out, really hope someone can shine on light on this.

I have a Calculated column called 'Age Day Breakdown (Invoice Received Date)' which asign a breakdown based on how long it takes for an invoice to get paid (e.g. 20 days, 20 -30 days etc) and i also have Measure called [nvoice Count] which DISTINCTCOUNT the Invoice keys to ensure it is only counted once for duplicate keys.

The requirement is I need to calculate the total of [Invoice Count] for the month and then divide it each 'Age Day Breakdown (Invoice Received Date)' of that month so this allows me to keep the constant of the percentage for each breakdown when i expand the months on a slicer.  hope this makes sense, thank you in advance!!

[deng2431 · Accepted solution]

Hey Guys, i realised what i asked for its not possible, i just ended up using 100% stacked column chart to solve this issue. thanks for taking the time to look into this. 

[deng2431]

Hi @v-luwang-msft ,

thank you for your response, below is my sample data

Below is also the measure for the Invoice Count

Invoice Count = DISTINCTCOUNT('Fact PTRS Full Detail list'[Invoice Key])

Invoice Amount	Payment Date (Final)	Age Day Breakdown (Invoice Received Date)	Invoice Key
$40,770.00	5/01/2022	Within 20 Days	300473631
$510,849.00	5/01/2022	Within 20 Days	300473632
$362,309.00	5/01/2022	Within 20 Days	300473633
$836,228.00	5/01/2022	31 - 60 Days	300473630
$544,799.00	5/01/2022	120 Days +	300473665
$268,948.00	5/01/2022	120 Days +	300473666
$737,459.00	6/01/2022	Within 20 Days	300473324
$734,107.00	6/01/2022	Within 20 Days	300473325
$267,842.00	6/02/2022	21 - 30 Days	300473361
$402,735.00	7/02/2022	21 - 30 Days	300473362
$624,928.00	8/02/2022	21 - 30 Days	300473363
$159,584.00	9/02/2022	Within 20 Days	300473629
$675,505.00	10/02/2022	Within 20 Days	300473657
$461,756.00	11/02/2022	Within 20 Days	300473658
$186,568.00	12/02/2022	Within 20 Days	300473660
$125,670.00	13/02/2022	120 Days +	300473664
$23,693.00	14/02/2022	120 Days +	300473689
$647,173.00	15/02/2022	120 Days +	300473691
$41,258.00	16/02/2022	120 Days +	300473694
$121,739.00	17/02/2022	Within 20 Days	300473696
$901,140.00	18/02/2022	Within 20 Days	300473698
$696,555.00	19/02/2022	21 - 30 Days	300471445
$620,056.00	20/02/2022	21 - 30 Days	300471446
$17,169.00	21/02/2022	21 - 30 Days	300471648
$746,812.00	22/02/2022	31 - 60 Days	300472117
$892,503.00	23/02/2022	32 - 60 Days	300472122
$459,311.00	24/02/2022	33 - 60 Days	300472123
$868,184.00	6/03/2022	34 - 60 Days	300472133
$5,346.00	7/03/2022	21 - 30 Days	300472215
$623,323.00	8/03/2022	21 - 30 Days	300472216
$249,457.00	9/03/2022	21 - 30 Days	300472217
$441,168.00	10/03/2022	Within 20 Days	300472251
$389,775.00	11/03/2022	Within 20 Days	300472252
$128,900.00	12/03/2022	Within 20 Days	300472253
$840,447.00	13/03/2022	Within 20 Days	300472255
$841,255.00	14/03/2022	21 - 30 Days	300472256
$890,314.00	15/03/2022	120 Days +	300472257
$934,827.00	16/03/2022	121 Days +	300472258
$369,981.00	17/03/2022	122 Days +	300472259

This is the expected result i am after

MonthLongCaption	Invoice Amount	Age Day Breakdown (Invoice Received Date)	Invoice Count	Total Month Count	Percntage
Jan-22	$65,779,620.90	Within 20 Days	5	10	50.00%
Jan-22	$4,844,804.25	21 - 30 Days	1	10	10.00%
Jan-22	$4,947,326.54	31 - 60 Days	1	10	10.00%
Jan-22	$85,465.27	61 - 90 Days	1	10	10.00%
Jan-22	$42,779.77	91 - 120 Days	0	10	0.00%
Jan-22	$34,257,770.86	120 Days +	2	10	20.00%
Feb-22	$85,790,248.32	Within 20 Days	6	19	31.58%
Feb-22	$10,737,667.71	21 - 30 Days	6	19	31.58%
Feb-22	$4,938,695.98	31 - 60 Days	3	19	15.79%
Feb-22	$410,902.39	61 - 90 Days	0	19	0.00%
Feb-22	$64,335.80	91 - 120 Days	0	19	0.00%
Feb-22	$40,746,044.79	120 Days +	4	19	21.05%
Mar-22	$390,408,235.95	Within 20 Days	4	11	36.36%
Mar-22	$9,702,974.30	21 - 30 Days	4	11	36.36%
Mar-22	$3,340,285.57	31 - 60 Days	1	11	9.09%
Mar-22	$157,769.50	61 - 90 Days	0	11	0.00%
Mar-22	$41,716.34	91 - 120 Days	0	11	0.00%
Mar-22	$30,003,013.22	120 Days +	2	11	18.18%

 I am using the Month Year column data from the Calendar table 

[grantsamborn]

A few comments and I apologize if I have misunderstood...

• The Sample Data has a total of invoices equal to $18.7M whereas with the Expected Results the total is around $686M according to my calculations based on the source data.  Additionally, in your table shown, the total seems to be around $866M.  There has to be a way to reconcile/prove any results.
• Why are there 2 fact tables in your model but your sample data only includes 1 table?
  • From the model view, it looks like there is a many-to-one relationship between ‘Fact PTRS Full Detail list’ and another table (‘Fact Invoice Register Transactions’?) except I’m not exactly sure how they are related.
• How are you calculating your calculated column [Age Breakdown (Invoice Received Date)]?
  • Why are there different categories that are open-ended?
    • “120 Days +”, “121 Days +”, “122 Days +”
  • (It would probably be better to make a new dimension table with all categories which has a sort column and try to incorporate that.)

pbix:    https://1drv.ms/u/s!AnF6rI36HAVkhPIB0y10OMKvPEVItw?e=yk4dhT

Let me know if you need any clarification.

[deng2431]

Hi @grantsamborn,

Thank you for taking the time regarding this and apologies that i have made this confusing. 

• the same data value doesnt really matter for the result, what i really care about is the distinct count [Invoice count] of the Invoice key column. the dollar amount didnt match up as i just did it with random number for the amount.
• Yes, i got 2 Fact tables but i am primarily using one 'Fact PTRS Full detail list', i use this fact table to use as a drill through into another fact table via the invoice numbers, as the 2 fact table shows different details for the same invoice number. but this setup shouldnt affect what this is trying to do.
• The calculated column [Age Breakdown (Invoice Received Date)] is using the below measure: 
  • Age Day Breakdown (Invoice Received Date) =
    Switch(true(),
        ('Fact PTRS Full Detail list'[Payment Date (Final)] - 'Fact PTRS Full Detail list'[Invoice Received Date]) > 120, "120 Days +",
        ('Fact PTRS Full Detail list'[Payment Date (Final)] - 'Fact PTRS Full Detail list'[Invoice Received Date]) >= 91, "91 - 120 Days",
        ('Fact PTRS Full Detail list'[Payment Date (Final)] - 'Fact PTRS Full Detail list'[Invoice Received Date]) >= 61, "61 - 90 Days",
        ('Fact PTRS Full Detail list'[Payment Date (Final)] - 'Fact PTRS Full Detail list'[Invoice Received Date]) >= 31, "31 - 60 Days",
        ('Fact PTRS Full Detail list'[Payment Date (Final)] - 'Fact PTRS Full Detail list'[Invoice Received Date]) >= 21, "21 - 30 Days",
         'Fact PTRS Full Detail list'[Payment Date (Final)] = BLANK(), "Unpaid in Full",
        ('Fact PTRS Full Detail list'[Payment Date (Final)] - 'Fact PTRS Full Detail list'[Invoice Received Date]) <= 20, "Within 20 Days )
    • regarding the different age down, “120 Days +”, “121 Days +”, “122 Days +”. that's my fat fingers when i enter it manually, it should be just “120 Days +" and not the rest.
    • thats a good idea with creating a dimension table for my categories, at the moment my table is pretty small, just over 120,000 rows. 

[grantsamborn]

Regarding the first point:  My results for the Total Month Count for the 3 months differ from yours.

	Mine	Yours
Jan	8	10
Feb	19	19
Mar	12	11

Is there any way you could come up with a pbix including both fact tables and your calculated columns and measures?  Also, expected results since the sample data will probably be different.

Fake data is fine if it can be used to demonstrate examples.  

My pbix: https://1drv.ms/u/s!AnF6rI36HAVkhPICoiZSqhiW2DeHLw?e=8usZw5

[deng2431]

Hi @grantsamborn,

Really appreciate you taking the time to help me out with this. thank you!

I have attached the pbix file with the fact table (i am only utilising one) with the calculated columns and measures. and also the expected result in an excel file. 

PBIX file - https://drive.google.com/drive/folders/1jCPpYhIfVp6vAcYyYB8NFUiD5Q7iYPl6?usp=share_link

thank you for your help in advance.

[grantsamborn]

Hi @deng2431 

For personal/health reasons, I am going to have to refer you to many others on this board including

@v-luwang-msft 

@amitchandak 

@Greg_Deckler 

I hope you find a solution.

[deng2431]

Hi @grantsamborn , thank you for looking into this for me, appreciate it!

Hope you have a speedy recovery and all the best!!

[v-luwang-msft]

Hi @deng2431 ，

Please share sample data and expected data results, remembering to delete confidential data.

Best Regards

Lucien