cancel
Showing results for
Did you mean:
Highlighted Helper III

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

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).

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User I

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

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

Proud to be a Super User!

4 REPLIES 4
Highlighted Super User I

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

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

Proud to be a Super User!

Highlighted Helper III

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

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?

Highlighted Super User I

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

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

Proud to be a Super User!

Highlighted Helper III

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

thanks. this solves my problem.

Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (2,637)