Measure Problem with all and blank rows (show correct percentage)

[elaj]

Hi, i have another thing i cannot solve by myself.

i want to have my measures show the correct values in the correct columns and lines.

i want to show percentages with the base and the Ns filtered by brands and waves in a matrix.

but i dont know how to do it.

i have this matrix:

1. in the upper red rectangle (F5_lbl: 10+ and  wave 3 and B2) i want to see the base of 478 too and N of 0 and percentage of 0%.

(set the N measure to +0 doesnt help)

2. and in the lower red rectangle i dont want to see the base.

(i know i can filter the visual by F5_lbl no empty rows.. so that the problem at the lower rectangle would be gone... but i want my measure doing this automatically).

thanks for your answers 🙂

PBIX Attached 

[dedelman_clng · Accepted solution]

Hi @elaj 

Step 1 - create a dimension table for F5_lbl

Lbl = FILTER(SUMMARIZE(data, data[F5_lbl], data[F5_lbl_sort]), not(ISBLANK([F5_lbl])))

We keep F5_lbl_sort with F5_lbl so you can sort the new table/column

Step 2 -  Make a relationship between Lbl and data

Step 3 - replace data[F5_lbl] with Lbl[F5_Lbl] in the rows of the matrix

Step 4, alter the measures as follows

Base (W) =
VAR __f5 =
    IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
    IF (
        __f5 <> "",
        CALCULATE ( SUM ( data[weight] ), //REMOVING THIS PART -> data[F5_lbl] <> BLANK(), 
            ALLNOBLANKROW ( Lbl ) ),
        BLANK ()
    )

For Base we remove the "data[F5_lbl] <> BLANK() as this is now handled by the model. The VAR and IF are to remove the blank rows at the end of the visual

N (W) =
VAR __f5 =
    IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
    IF ( __f5 <> "", CALCULATE ( SUM ( data[weight] ) ) +0 )
//, KEEPFILTERS(data[F5_lbl] <> BLANK()))

For N we remove KEEPFILTERS as this is now handled by the model. The VAR and IF are to remove the blank rows at the end of the visual

Now, it did add B6 to the report, but if this is a problem we should be able to work around that.

Hope this helps

David

[elaj]

Wow @dedelman_clng . Thanks for this detailed answer. Yes this helps. But yea. If one Brand has no base.. so no probands had the chance to answer, the zero in B6 is not really true. it should be "missing"... in power bi it should be blank. You said there is a way to work that out?

And i have another question: why is b6 now only visible in wave 3?

[dedelman_clng]

Hi @elaj 

B6 got "added" because it is a value for "Brand" in the main table, and so it is in the Lbl dimension table. It is on the chart because N is 0.

To remove the zero from B6, change the code for N(W) to

N (W) =
VAR __f5 =
    IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
    IF (
        NOT ( ISBLANK ( [Base (W)] ) ),  //<-- Added this IF stmt
        IF ( __f5 <> "", CALCULATE ( SUM ( data[weight] ) ) + 0 )
    )

Now you will no longer see B6 in the chart. If you do, however, want to see the empty column, click on F5_Brand_OC_Top in the fields window and choose "Show items with no data"

Hope this helps

David

[elaj]

thanks. this solves my problem.