cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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:

image.png

 

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
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

 

2020-08-10 10_07_54-N_and_BASE_problem (3) - Power BI Desktop.png

 

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

2020-08-10 10_24_48-N_and_BASE_problem (3) - Power BI Desktop.png

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!




View solution in original post

4 REPLIES 4
Highlighted
Super User I
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

 

2020-08-10 10_07_54-N_and_BASE_problem (3) - Power BI Desktop.png

 

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

2020-08-10 10_24_48-N_and_BASE_problem (3) - Power BI Desktop.png

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!




View solution in original post

Highlighted
Helper III
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
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"

 

2020-08-11 07_21_24-N_and_BASE_problem (3) - Power BI Desktop.png

 

Hope this helps

David




Proud to be a Super User!




Highlighted
Helper III
Helper III

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

thanks. this solves my problem.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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 Kudoed Authors