Hide rows if a column is empty

[pi8080]

Hi all,

I am trying to build a DAX which would show only those periods where we have both actuals and budget data.

Let's assume I have 12 periods, one for each month of the year and that I have data for the whole year for budget but only for the first two months of the year forr Actuals.

If that is the case I would like to have a table which shows only period 1 and 2 and when i will have actuals for March too it would show period 1, 2 and 3 and so on.

The formula I am using at the moment to calculate the variance between actuals and budget in a forth column is correct but show amounts also when i have no data for Actual months so I would need to amend it so that only months with both Actuals and Budget are displayed :

IF(HASONEFILTER(Actuals[actual], Blank(), Sum(Actuals[actual]) - Sum(Budget[budget]))

Many thanks for your help

[v-weiyan1-msft]

Hi @pi8080 ,

I am not sure if I understood your question correctly.
Based on the information you provided, I assume you have two tables: Actuals and Budget.
Here is the dummy data I created.
Table-Actuals

Table-Budget

Please try code as below.

Variance = 
IF (
    SELECTEDVALUE ( Actuals[Actual] ) = BLANK (),
    BLANK (),
    SUM ( Actuals[actual] ) - SUM ( Budget[budget] )
)

Result is as below.

Is this the result you expect?

If I've misunderstood you, can you share sample data and the results you are hoping for? Show it as a screenshot or excel. Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pi8080 · Accepted solution]

Hi Yulia, thanks for your reply.

I found the solution by adding a filter using Actuals Posting Period. Now only the periods displayed in the Actuals table will be shown in the PowerBI table which is what I wanted. ( Before i could see 12 periods even though I had only two months worth of data for Actuals). The only downside with this method is that the slicer is visible in the report view.

Thanks again

Piero

Before I could see 12 periods in the BI table cause the Budget file has 12 periods but by using the filter I can match it to the actuals periods so tba

[pi8080]

I found another formula which show just the periods when both columns have data however it doesnt calculate the variance between the two columns and the actuals column display the same amount in both rows in period 1 and 2 ( the amount displayed in each row is the total amount)

The formula is = If(ISFILTERED(Actuals[postingperiod]),calculate(sum(actuals[actual]),FILTER('actuals',NOT('actuals'[postingperiod] in VALUES('actuals'[posting period])))))

[pi8080]

Sorry I forgot to mention that I added a slicer with the posting period from the Actuals table that drives what periods should be displayed