Force table to show blanks

[Rasmusrock]

Hi everyone,

I have a problem with the table visual in PowerBi Desktop.

Do anybody know if you can force it to show blanks?

My example is this:

If you have a general ledger with 12 accounts. Revenue, production costs, contribution margin etc. but lets say nothing has been charged on one of the accounts in a given month. Then this account will no longer be shown in the table if you slice on this particular month. Is there any way to force the table to show the account even though it is blank??

Best regards,

/Rasmus 

[Sean]

@Rasmusrock I know exactly what your talking about - and it is a source of big frustration...

You want to force ALL dates to show (whatever they may be in the Calendar) regardless whether there's an entry or not

@Greg_Deckler's solution works but it has very limited use (I use column = 0 because in a stacked column chart - it adds nothing)

As soon as you try to break down the data in a table or a visualization it again removes the dates where there are no entries

In the picture you can't place any field in the Column Series (in the Chart) and you can place another field in the table but then the dates with no entries disappear...

EDIT: Try creating a Measure that simply sums this column = 0 and then add this Measure to your table/matrix

Then drag with the mouse to hide it as in the picture...

This seems to help with the Matrix but not with the Charts

 

[Rasmusrock]

Hi @Sean,

Thanks for the input, however, it does not solve my problem.. I have put a bit more work into it, and hardcoded my data into PowerPivot in Excel, to check if it works here - and it does!!

My output when i insert a table in PowerBi Desktop:

And my output in excel:

Exactly the same tables, relations and measures are used in both scenarios.. So does anybody have a clue on why it shows data in the 'dækningsbidrag', 'driftsresultat' and 'resultat før skat' rows in Excel and not in PowerBI Desktop when i slice the data on year and month?

Best regards,

/Rasmus

[Greg_Deckler]

What is your formula in Power BI for Realiseret?

[Rasmusrock]

Hi @Greg_Deckler,

It is a combination of 4 formulas:

We start with, where 'transaktionstabel' is the transactions from the general ledger.

Corrected Amount = Calculate(SUM(Transaktionstabel[Amount])*-1;Kontoplan[Account Type]=0)

Then we use:

Running totals = IF(HASONEFILTER(Header[Header]);CALCULATE(Transaktionstabel[Corrected Amount];ALL(Header[Header]);Header[Order]<VALUES(Header[Order]));BLANK())

Over into:

Combined = SWITCH(Header[Header CalcType];BLANK();BLANK();1;[Corrected Amount];2;[Running totals])

and end with:

Realiseret = IF(AND([Header Detail]=0;[Is Filtered]);BLANK();[Combined])

------------------

I hope you understand it, otherwise ill gladly provide more information 🙂

/Rasmus

[Nhallquist]

@Greg_Deckler had a good solution on a different post.  In the Data section, create an IF statement that uses ISBLANK to find the Blank field, and puts in "$0" where it is blank, ELSE the field value.  

That should work as well.

Nate

[Greg_Deckler]

You could try this, create a column like:

Column = 1

Add that to your table visualization and then drag the column width so that it disappears. Since that column will always contain a value all of your table rows should display in the table.