Calculate the Total Cash for different BUs

[Anonymous]

Hello all,

I'm trying to migrate an Excel based Sales Report to Power BI. One of the sheets is a Cash Balance sheet where we have the current Cash Balance of each BU.

In the attached screenshots, the section marked as "Rest" are the BU's that are stored in a FACT table and has transaction details by each day. The section marked as "X", is a different BU, whose details I receive as a spreadsheet containing two columns (Reporting Date[Date] and Balance[Decimal]).

So far I've been able to do the following in PBI (screenshot 2):

1. For the "Rest" BUs I can show Total Cash in a matrix (BU name in Rows, Cash balance in Values, and applying a filter on Reporting Date = is on or before 30 April)
2. For BU "X", created a measure as: LOOKUPVALUE ('[Balance]', [Reporting Date], Today()-12)

However I'm struggling to add 1 and 2, to show a Total Cash in the PBI report. How can I show the Total Cash (as in the screenshot 1) for all the BUs (Rest + X) for April 30 in the PBI report.

Thanks in advance. Any ideas are appreciated.

Cheers, S.

[mahoneypat]

From your description, it sounds like it might be best to append the two data sources together.  Rename the columns (if needed) in source 2 to match source 1 in the query editor and then hit Append.  You will then have a simpler model to do analysis on.  Simple model, simple DAX.

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat,

Thanks for your response.

I did try appending both the tables as a new table and use the basic SUM as a measure, but it's not giving the desired result.

The first table (that stores the BUs marked as REST) is a FACT table which has transactions by each day (i.e. one BU can have multiple transactions a day). So if I want a overview of the Cash balance of one BU as of 30-April, I have to sum up all the $/€ Balance for that BU up to 30-April.

The second table (that stores BU X) is a Excel sheet and the rows contain a consolidated amount for a single day. So 30-April will have only one row.

So if I define a measure Total Cash = Sum (Balance_EUR) in the appended table, and apply a date filter on the matrix visual "on or before 30-April" I get the correct result for the REST BUs.
However, for BU X, it also adds up all the values before 30-April, where as, I need the value of only 30-April.

I could do it quite easily in Excel by pivoting them separately and adding the totals (Screenshot 1) but struggling to implement it in PBI.

[Anonymous]

Hi @Anonymous 

You should bring both tables to the same denominator. The one with BU X is already in the correct shape. Now just use Power Query to aggregate the values for each BU other than X so that each day you've already got the right amount (without having to sum in DAX from beginning to the latest date visible in the current context). Then you can UNION both tables (stack them up in PQ), and then create one, simple and homogenous measure. The measure would get the latest values in the current context for each BU and sum them up. This is not only simple but also efficient and fast since only Storage Engine's pass can be used to sum these up.

Best
D

[mahoneypat · Accepted solution]

Have you tried an approach like this?

NewMeasure =
VAR selecteddate =
MIN ( Date[Date] ) //assumes you have a Date table and a single value is selected via slicer or table row
VAR RESTsum =
CALCULATE (
SUM ( REST[ValueColumn] ),
FILTER ( ALL ( Date[Date] ), Date[Date] <= selecteddate )
)
VAR Xsum =
SUM ( Xtable[ValueColumn] ) // again, assumes single date is selected
RETURN
RESTsum + Xsum

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat,

Thank you for the formula!!! I'm still a DAX/Power BI newbie so I haven't started to write complex formulas yet, but your formula certainly helped me to achieve my target. I've modified it as below:

[Measure] =
Var selectedDate = CALCULATE(MIN('PBI Calendar'[Date]),ALLSELECTED('PBI Calendar'[Date]))
Var RESTsum = CALCULATE(SUM('Sales'[Balance_EUR]),('PBI Calendar'[Date] <= selectedDate),'Sales'[Entity] <> "X")
Var Xsum = CALCULATE(SUM('Sales'[Balance_EUR]),('PBI Calendar'[Date] = selectedDate),'Sales'[Entity] = "X")
Return
RESTsum + Xsum

I do have to use a slicer though and select the date for this formula to work. However, the Date field contains dates from 2015 and I have to scroll down the slicer to select the desired date.

Is there a way I can create a prompt in the page, that asks me to fill in a date, and the entered date gets stored in a variable, which I can use in my measure? Sorry if it sounds like a stupid question, but I'd like it to be a bit more dynamic 🙂

Cheers,

S. 

[mahoneypat]

Yes. You can add text box with instructions, or you can use visual tooltips (pop up with instructions; see video by Reid Havens).

For the slicer, you could add another one column Date table (e.g., DateForSlicer = Values(Calendar[Date]) with no relationship to your other tables.  Then make a slicer with that column and add a variable  to your measure like var DateFromSlicer = SelectedValue(DateForSlicer[Date]) and then use that later in your measure.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Great. I'll check it out/implement and let you know if it worked.

Thanks again, @mahoneypat for your earlier formula.

Cheers,

S.

[Greg_Deckler]

Sorry, @Anonymous but I can't follow this. I don't really have any sense of your source data and I have no idea really what is going on in the visuals you posted or what the problem is. It is probably because I haven't had any coffee yet this morning.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

On a wild guess, perhaps this is a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])

SumScoreMeasure = SUMX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

So you could do that for 1 or maybe it is 2 and then add it to your 2 or maybe it is 1.

[Anonymous]

Hello @Greg_Deckler,

Thank you for the response and my apologies for the confusion. Attaching my dataset in table below.

1. Table "Cash Balance Other BU" is a Fact table in the DWH which gets loaded everyday. There can be multiple transaction rows for a single entity for the same Value date.

Table "Cash Balance XYZ" is a spreadsheet that gets uploaded to a Sharepoint site and has limited columns. There is only one row for a given date and reflected the total cash balance as of that day.

2. Expected output is to show the total Cash balance of all the entities in a single table in Power BI. In Excel, I could easily pivot them and show the total cash in one table (screenshot 1), but struggling to do so in PBI.

Cash Balance Other BUs

Entity	Year	Period	ValueDate	CashIn_EUR	CashOut_EUR	Balance_EUR
ABC	2020	4	30/04/2020	3672,5		3672,5
ABC	2020	4	24/04/2020	1655,7		1655,7
MNO	2020	4	30/04/2020		-500	-500
MNO	2020	4	25/04/2020		-38908,75	-38908,75
MNO	2020	4	24/04/2020		-4252	-4252
PQR	2020	4	30/04/2020	8794,864516		8794,864516
PQR	2020	4	25/04/2020	4895,455035		4895,455035
PQR	2020	4	24/04/2020	796,5000797		796,5000797
JKL	2020	4	30/04/2020	60,5605		60,5605
GHI	2020	4	30/04/2020	25235,02		25235,02

Cash Balance XYZ

Reporting Date	Cash Amount	Entity
20/04/2020	2.246.277,65	XYZ
21/04/2020	2.259.165,49	XYZ
22/04/2020	2.241.753,49	XYZ
23/04/2020	2.259.628,58	XYZ
24/04/2020	2.306.439,38	XYZ
25/04/2020	2.306.439,38	XYZ
26/04/2020	2.306.439,38	XYZ
27/04/2020	2.317.568,18	XYZ
28/04/2020	2.317.568,18	XYZ
29/04/2020	2.092.911,46	XYZ
30/04/2020	2.090.705,10	XYZ