cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Help with a measure please

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:

 

Code01/04/201801/05/201801/06/201801/07/201801/08/201801/09/201801/10/201801/11/201801/12/201801/01/201901/02/201901/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:

 

Code01/04/201801/05/201801/06/201801/07/201801/08/201801/09/201801/10/201801/11/201801/12/201801/01/201901/02/201901/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:

 

Code01/04/201801/05/201801/06/201801/07/201801/08/201801/09/201801/10/201801/11/201801/12/201801/01/201901/02/201901/03/2019
BACB       20.620.620.620.620.6
BACD       75.475.475.475.475.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:

 

Code01/04/201801/05/201801/06/201801/07/201801/08/201801/09/201801/10/201801/11/201801/12/201801/01/201901/02/201901/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

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Help with a measure please

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Help with a measure please

I have tried this - thanks

 

I am getting an error message:  "Resources Exceeded - this visual has exceeded the available rrsources"

 

Any ideas?

Highlighted
Helper I
Helper I

Re: Help with a measure please

I have the figure which I need to spread out as a separate table. Code and Value

Problem is when I try and distribute it I end up with the original table with amounts per month and not I. The Forecast months!

Somehow I need to reference the total and Code, and then add that vale to each total for the Code in the Forecast months only!

Any ideas how I could achieve that?

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors