Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I was wondering if someone could help me with a measure please.
I have a very large data file (25 columns x 600,000 rows) which contains a vast amount of data, but eventually i have managed to produce a Matrix in a report that looks like:
Code | 01/04/2018 | 01/05/2018 | 01/06/2018 | 01/07/2018 | 01/08/2018 | 01/09/2018 | 01/10/2018 | 01/11/2018 | 01/12/2018 | 01/01/2019 | 01/02/2019 | 01/03/2019 |
BACB | 245 | 306 | 383 | 479 | 598 | 748 | 935 | 1,168 | 1,460 | 1,825 | 2,282 | 2,852 |
BACD | 221 | 276 | 345 | 431 | 538 | 673 | 841 | 1,051 | 1,314 | 1,643 | 2,054 | 2,567 |
BACC | 466 | 582 | 727 | 909 | 1,136 | 1,421 | 1,776 | 2,220 | 2,775 | 3,468 | 4,335 | 5,419 |
BACE | 419 | 524 | 655 | 818 | 1,023 | 1,279 | 1,598 | 1,998 | 2,497 | 3,121 | 3,902 | 4,877 |
BAGG | 884 | 1,106 | 1,382 | 1,727 | 2,159 | 2,699 | 3,374 | 4,217 | 5,272 | 6,590 | 8,237 | 10,296 |
BABG | 429 | 536 | 670 | 837 | 1,046 | 1,308 | 1,635 | 2,044 | 2,555 | 3,193 | 3,992 | 4,989 |
TOTAL | 2,663 | 3,329 | 4,161 | 5,201 | 6,501 | 8,127 | 10,158 | 12,698 | 15,873 | 19,841 | 24,801 | 31,001 |
In the report there are more Codes, but i have reduced it for simplicity.
The table above comes from 2 x measures:
The values in RED are payments that have been Invoiced and Paid to date.
The values in BLACK are forecasted payments to be made in the future months.
I have a measure that basically says, if the Date >= 1/11/18 use the forecasted values, otherwise use the invoiced and paid data.
That all works. 🙂
MY ISSUE
Some invoices received are not paid straight away, but i know we will be paying them at some time in the future. For example we may withold these payments in the respective months:
Code | 01/04/2018 | 01/05/2018 | 01/06/2018 | 01/07/2018 | 01/08/2018 | 01/09/2018 | 01/10/2018 | 01/11/2018 | 01/12/2018 | 01/01/2019 | 01/02/2019 | 01/03/2019 |
BACB | 45 | 58 | ||||||||||
BACD | 225 | 152 |
I have a Measure that works out what has not been paid, and which Code.
What i need help with is as follows.
I need to take the total for each Code, and spread it out across the remaining months:
Code | 01/04/2018 | 01/05/2018 | 01/06/2018 | 01/07/2018 | 01/08/2018 | 01/09/2018 | 01/10/2018 | 01/11/2018 | 01/12/2018 | 01/01/2019 | 01/02/2019 | 01/03/2019 |
BACB | 20.6 | 20.6 | 20.6 | 20.6 | 20.6 | |||||||
BACD | 75.4 | 75.4 | 75.4 | 75.4 | 75.4 |
And then i need to add these values into the Measure i have to obtain the data on teh right of my table, so i end up with:
Code | 01/04/2018 | 01/05/2018 | 01/06/2018 | 01/07/2018 | 01/08/2018 | 01/09/2018 | 01/10/2018 | 01/11/2018 | 01/12/2018 | 01/01/2019 | 01/02/2019 | 01/03/2019 |
BACB | 245 | 306 | 383 | 479 | 598 | 748 | 935 | 1,189 | 1,481 | 1,846 | 2,302 | 2,873 |
BACD | 221 | 276 | 345 | 431 | 538 | 673 | 841 | 1,127 | 1,390 | 1,718 | 2,129 | 2,642 |
BACC | 466 | 582 | 727 | 909 | 1,136 | 1,421 | 1,776 | 2,220 | 2,775 | 3,468 | 4,335 | 5,419 |
BACE | 419 | 524 | 655 | 818 | 1,023 | 1,279 | 1,598 | 1,998 | 2,497 | 3,121 | 3,902 | 4,877 |
BAGG | 884 | 1,106 | 1,382 | 1,727 | 2,159 | 2,699 | 3,374 | 4,217 | 5,272 | 6,590 | 8,237 | 10,296 |
BABG | 429 | 536 | 670 | 837 | 1,046 | 1,308 | 1,635 | 2,044 | 2,555 | 3,193 | 3,992 | 4,989 |
TOTAL | 2,663 | 3,329 | 4,161 | 5,201 | 6,501 | 8,127 | 10,158 | 12,794 | 15,969 | 19,937 | 24,897 | 31,097 |
Welcome any help.
Many thanks
OK, there is a lot here but not sample source data so some of this is guessing. But, if you have a measure that calculates what has not been paid, then you could write a measure like this:
Measure = VAR __code = MAX([Code]) //current code in matrix VAR __table = SUMMARIZE(ALL('Table'),[Code],[Month],"__2BPaid",[Measure to be Paid]) //summarize your data table by code, month (date) and your measure calculation of what remains to be paid RETURN SUMX(FILTER(__table,[Code] = __code),[__2BPaid]) //filter your table down to your code and sum up the 2BPaid column giving you the total to be paid
That should give you the total to be paid. I can only surmise that you are able to get a count of the remaining months somehow so then you just divide by that to get a number for each future month and then you should be good.
I have tried this - thanks
I am getting an error message: "Resources Exceeded - this visual has exceeded the available rrsources"
Any ideas?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |