Forum Discussion

Tarek78210's avatar
Tarek78210
Frequent Visitor
2 years ago

Power Pivot showing all data with no values

Hi All,

The image below is showing the actual revenue and budgeted revenue per customer (variance in € and in %).

 

For some reasons, the pivot table is showing the list of all customers even if there is no budget or actuals (all the rows at -100%).

 

How can I stop showing all those empty rows showing -100%? If it is a genuine -100% (meaning there was a budget amount but no actuals), I want to show it. However, when there are no budget nor actuals, I don't want to show this in my pivot.

 

Thanks,

Need Help
Tips and Tricks

3 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    Tarek78210 

     

    if column H is a measure called measure_h and second measure called actuals /

     

    measure_h = 

    //code here

    var variance=  code here

    return 

    if( isblank([actual]) ,  blank() ,  variance ) 

     

     

    let me know if this helps .

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This DAX pattern should work

    Measure = if(and([Actual]=blank(),[Budget]=blank()),blank(),[your measure])

    Hope this helps.

  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    2 months ago

    To suppress rows where both budget and actuals are blank (keeping only genuine -100% rows where budget exists but no actuals):

     
     
    Show Row =
    VAR _actual = [Actual Revenue]
    VAR _budget = [Budget Revenue]
    RETURN
    IF(ISBLANK(_actual) && ISBLANK(_budget), 0, 1)

    Then add this as a Visual-level filter: Show Row = 1. This keeps rows where either value exists, including genuine -100% variance rows.

    Alternatively in the visual filter pane: filter [Budget Revenue] to "is not blank" OR [Actual Revenue] to "is not blank" no DAX needed.

    For teams running budget vs actuals reporting regularly where Finance users need to add/remove variance columns themselves, Flexa Tables on AppSource handles the variance layer without DAX