Forum Discussion

bencefarsang's avatar
bencefarsang
New Member
5 years ago
Solved

How to remove empty rows with missing data from a calculated table chart?

I've got the following table chart about sales data. The third and fourth columns are calculated by time intelligence (month-over-month and year-over-year changes). I haven't got any data from 2020M1...
Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    bencefarsang 

    Just follow the pattern shown earlier: 

     

    Sales MoM% V2 =
VAR result_ =
    IF (
        ISFILTERED ( 'Table1'[DATE] );
        ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." );
        VAR __PREV_MONTH =
            CALCULATE (
                SUM ( 'Table1'[Sales] );
                DATEADD ( 'Table1'[DATE].[Date]; -1; MONTH )
            )
        RETURN
            DIVIDE ( SUM ( 'Table1'[Sales] ) - __PREV_MONTH; __PREV_MONTH )
    )
RETURN
    IF ( result_ = -1; result_ )

     

    or if you want to do the check on Sales (using your current [Sales MoM%] measure

    Sales MoM% V2 =
VAR result_ = [Sales]
RETURN
    IF ( NOT ISBLANK(result_); [Sales MoM%] )

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

dobregon's avatar
dobregon
Impactful Individual
5 years ago

Hi bencefarsang 

In your case , i would make that the 2 measures sales Mom and Sales Yoy depend of the measure sales. if the sales is blank, the measure should be blank

 

Sales Mom = 
Var A = your measure of sales Mom
RETURN
IF(ISBLANK(SALES),BLANK(),A)


Sales Yoy = 
Var A = your measure of sales Yoy
RETURN
IF(ISBLANK(SALES),BLANK(),A)

 

 

With this, if the measure sales doenst have values you will not see any values in the other % columns

Then you need to check if you have disabled the option 

 


by default i think it is disabled. if it is disabled you will not see the row if all the columns in that row have blank values. Hope this helps