Forum Discussion

pi8080's avatar
pi8080
Regular Visitor
2 years ago
Solved

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

  • pi8080's avatar
    pi8080
    2 years ago

    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

4 Replies

  • pi8080's avatar
    pi8080
    Regular Visitor
    2 years ago

    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's avatar
      pi8080
      Regular Visitor
      2 years ago

      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

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    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's avatar
      pi8080
      Regular Visitor
      2 years ago

      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