Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

Calculate running total for missing values, showing blank

Hi there, 

 

I am building a report to represent the cumulative of a column (the cashflow) over time (different years) based on some categories (i.e. different asset_types). The problem is that not all the years have an equivalent value and consequently these years are shown blank in my bar chart. I was wondering if someone let me know how to fix my DAX query.

 

3-Cumalative Cash Flows =

CALCULATE (sum (Output_SSIS [Cash Flow]),
filter(
ALLselected (Output_SSIS),
Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year])
),VALUES(Output_SSIS[Asset type])
)
-- This is shown as the value for each column
 

image.png

 

Total Cumalative Cash Flow = CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS[Financial Year]<=Max(Output_SSIS[Financial Year])))
This is shown as line on top of the chart
 
image.png
 
The orange category (asset_type) does not have any record(cash flow) for the last three years of the x-axis and that is the reason it is shown as blank.
 
Any help is highly appreciated.
 
Thanks
 
6 REPLIES 6
Anonymous
Not applicable

This is what happens when you build a model that does not follow Best Practices, that is, the star schema. Please build a correct model and your problems will be gone. Here's something to get you started: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Here's why you've got problems of this kind. You've only got one big table, Output_SSIS, and for some dates some types are not present in it. Therefore, when you start filtering, some values will be missing in the context. In your case it's VALUES(...) that is causing the problem (in fact, you're causing the problem, not the function). If you use a proper dimension for Asset Type, this problem will immediately be gone since choices made in dimensions stay put regardless of what's in the fact table. Please therefore structure your models correctly in the first place.
Anonymous
Not applicable

@Anonymous 

 

Hi there, thanks for the link, I never thought these issues can be fixed with an appropriate data model. I was always seeing these concepts in the field of data model not DAX queries. The link looks very interesting, I will go through this (and the if clause) over the next few days and report back. 

Anonymous
Not applicable

@Anonymous

If you think about it deeply, it only makes sense that your graph looks the way it does. Think about the financial year where you don't see the values. You don't see them because in your formula you put VALUES(Output_SSIS[Asset type]). But for these years, when they are visible in the context, there are NO asset types, so your filter is empty in these years and this, of course, affects all the years that are filtered in your formula, hence your measure must return nothing. This all stems from the fact that you're taking all values from the fact table (one-table models should never be used in professional settings, only star schemas). If you had a dimension with your types, your selected type(s) (say, via a slicer, for instance) would be visible all the time regardless of other choices elsewhere. Hence, your formula would work OK without even putting VALUES(...) in the formula. Just stick to correct models and you'll be safe.
Anonymous
Not applicable

Hi @Anonymous  ,

 

Thanks again for the explanation and also the link. It was very educational,.

 

I have formed a star schema by breaking my big table into smaller ones by some DAX expressions such as 

Dim_Asset_type = DISTINCT(Output_SSIS[Asset Type])
 
So the whole * schema is shown below:

Sichanm_0-1603141431328.png

 

I put some filtering options below my chart which are coming from these dimensions and I use them to filter the chart values.

But still do not know how to modify my cumulative and total value (also a dax expression) to use these filters. The Y axis shows incorrect values, repetetive values, I guess it is because of the "allselected" function in the dax, please see below

image.png

Now my cumulative is :

Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS [Financial Year] <= Max (Year_Table[Financial Year])))
 
It shows the year correctly (i.e. no missing values) but it does not bring the correct values as I explained earlier. I appreciate that if you could advise.
 
and another try:
Sichanm_0-1603152744004.png
Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS [Financial Year] <= Max (Year_Table[Financial Year])),FILTER(Output_SSIS,Output_SSIS[Asset Type]=ALLSELECTED(Dim_Asset_type[Asset Type])),FILTER(Output_SSIS,Output_SSIS[Project Type]=ALLSELECTED(Dim_PrjType[Project Type])),FILTER(Output_SSIS,Output_SSIS[Precinct]=ALLSELECTED(Dim_Precinct[Precinct])))
 
 
And another try
 

image.png

Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(ALLSELECTED(Output_SSIS),Output_SSIS [Financial Year] <= Max (Year_Table[Financial Year]) && Output_SSIS[Asset Type] in ALLSELECTED(Dim_Asset_type[Asset Type]) && Output_SSIS[Project Type] in ALLSELECTED(Dim_PrjType[Project Type]) && Output_SSIS[Precinct] in ALLSELECTED(Dim_Precinct[Precinct])))

 

 I think I have fixed that, finally !!, I had to put values at the end, so the code is :

Sichanm_0-1603163063561.png

 

Cumulative Cash Flow = CALCULATE (sum (Output_SSIS [Cash Flow]),filter(filter(ALLSELECTED(Output_SSIS),Output_SSIS[Financial Year] <= Max (Year_Table[Financial Year])), Output_SSIS[Asset Type] in ALLSELECTED(Dim_Asset_type[Asset Type]) && Output_SSIS[Project Type] in ALLSELECTED(Dim_PrjType[Project Type]) && Output_SSIS[Precinct] in ALLSELECTED(Dim_Precinct[Precinct])),VALUES(Dim_Asset_type[Asset Type]))
 
But if this is the solution, can you explain why the values should be at the end, it seems it select distinct values but sitll cannot figure it out why I need to enforce it
 
Thanks

 

 

amitchandak
Super User
Super User

@Anonymous ,

Try like

CALCULATE (sum (Output_SSIS [Cash Flow]),
filter(
ALLselected (Output_SSIS),
Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year])
)
)

 

or

CALCULATE (sum (Output_SSIS [Cash Flow]),
filter(
ALLselected (Output_SSIS),
Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year]) && Output_SSIS[Asset type] = max(Output_SSIS[Asset type])
)
)

 

Anonymous
Not applicable

@amitchandak 

 

Thanks Amit, but none of them works

 

image.png

 

and the second one

image.png

 

I think I need to modify the calculation somehow to force it to use Output_SSIS [Financial Year]-1 if isblanck(Output_SSIS [Financial Year])  with some if statements possibly with some variables, but not sure exactly how but working on it

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.