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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pi8080
Regular Visitor

Hide rows if a column is empty

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

1 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

View solution in original post

4 REPLIES 4
v-weiyan1-msft
Community Support
Community Support

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

vweiyan1msft_0-1709002914491.png

Table-Budget

vweiyan1msft_1-1709002925530.png

Please try code as below.

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

Result is as below.

vweiyan1msft_2-1709003102945.png

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.

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
Regular Visitor

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])))))

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.