Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JohnJairoV
Helper I
Helper I

Help with Percentage with conditions - DAX formula

Hi to all!

 

I have this table into Excel:

 

TituloMesItemvalor
LibrosEneroLibro 130
LibrosEneroLibro 250
LibrosEneroLibro 320
LibrosEneroLibro 450
LibrosEneroLibro 540
LibrosEneroLibro 620
Video JuegosEneroVideo Juego 170
Video JuegosEneroVideo Juego 250
Video JuegosEneroVideo Juego 340
MusicaEneroCD115
MusicaEneroCD210
MusicaEneroCD320
MusicaEneroCD450
LibrosFebreroLibro 110
LibrosFebreroLibro 215
LibrosFebreroLibro 320
LibrosFebreroLibro 425
LibrosFebreroLibro 532
LibrosFebreroLibro 620
Video JuegosFebreroVideo Juego 170
Video JuegosFebreroVideo Juego 250
Video JuegosFebreroVideo Juego 340
MusicaFebreroCD115
MusicaFebreroCD210
MusicaFebreroCD320
MusicaFebreroCD450

 

I have a calculated column that works fine, for calculate the percentage of each item based on the Libros total:

 

=Tabla1[valor]/sumx(filter(Tabla1;Tabla1[Titulo]="Libros");Tabla1[valor])

 

But it's not calculate like I want when I filter by month:

 

PivotTable.png

 

Any ideas?  Thx in advance for your answers.

 

 

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@JohnJairoV Sorry I totally misread what you were asking for. Smiley Happy

 

But I think we got it this time really. Let me know!

 

% of MEASURE =
DIVIDE (
    SUM ( Table1[valor] );
    CALCULATE (
        SUM ( Table1[valor] );
        FILTER ( ALLSELECTED ( Table1 ); Table1[Titulo] = "Libros" )
    );
    0
)

 

2016-10-28 - % Of Item Total.png

View solution in original post

9 REPLIES 9
Sean
Community Champion
Community Champion

@JohnJairoV This shoud work...

 

Measure =
DIVIDE (
    SUM ( Table1[valor] );
    CALCULATE ( SUM ( Table1[valor] ); ALL ( Table1[Item] ) );
    0
)

2016-10-28 - % Of Total.png

Hi Sean and thx for your answer.

 

This is not that I want.  Sorry If I not explain the problem well.  

I want the percentage relative to a libros total.

 

For more understanding, must be like the images below:

 

Pivot Table before filter (this is calculated well):

Pivot Table Without FilterPivot Table Without Filter

Pivot Table in Enero (January):

Pivot Table with Enero (January) FilterPivot Table with Enero (January) Filter

 

Pivot Table with Febrero (February) Filter:

Pivot Table with Febrero (February) FilterPivot Table with Febrero (February) Filter

Sean
Community Champion
Community Champion

@JohnJairoV Are you doing this in Excel or PBI?

 

If in Excel drag the "Sumda de Valor" field again to the Values area of the pivot table.

Click in the newly added column => right-click => select Show Values As => % of Parent Row Total

 

Sean
Community Champion
Community Champion

@JohnJairoV Okay I think I got it in PBI too! Smiley Happy

 

Here is your % of Parent Row Total Measure (you'll have to change the , to ; )

 

% of Parent Row Total Measure = 
IF (
    ISFILTERED ( Table1[Item] ),
    DIVIDE (
        SUM ( Table1[valor] ),
        CALCULATE ( SUM ( Table1[valor] ), ALL ( Table1[Item] ) ),
        0
    ),
    DIVIDE (
        CALCULATE ( SUM ( Table1[valor] ), ALL ( Table1[Item] ) ),
        CALCULATE ( SUM ( Table1[valor] ), ALLSELECTED ( Table1 ) ),
        0
    )
)

 

 2016-10-28 - % Of Parent Row Total.png

Hi Sean.  Thx for the new answer but not is the answer that I looking for.

 

Please Check the "Must be" next to each Pivot Table in the photos.  I calculated the results with formula, and this is the expected answer, not the shown in the Pivot Table (Only the unfiltered Pivot Table has the right answer).

 

All the percentages must be calculated respect to the total of Libros, and not each quantity for his own total.

 

Thx again.  I hope that you did understood my requirement. (sorry for my english, is not my native language) Blessings!

 

 

 

 

 

 

 

Sean
Community Champion
Community Champion

@JohnJairoV Sorry I totally misread what you were asking for. Smiley Happy

 

But I think we got it this time really. Let me know!

 

% of MEASURE =
DIVIDE (
    SUM ( Table1[valor] );
    CALCULATE (
        SUM ( Table1[valor] );
        FILTER ( ALLSELECTED ( Table1 ); Table1[Titulo] = "Libros" )
    );
    0
)

 

2016-10-28 - % Of Item Total.png

Sean
Community Champion
Community Champion

% of MEASURE =
DIVIDE (
    SUM ( Table1[valor] ); <= NUMERATOR - sum all [valor] values within the current context
    CALCULATE (            <= DENOMINATOR 
        SUM ( Table1[valor] ); <= again sum all [valor] values but this time filter table1 first
        FILTER ( ALLSELECTED ( Table1 ); Table1[Titulo] = "Libros" ) <= so only rows where
    );                                     <= [tutilo] is "Libros" are included in the SUM 
    0 <= The value returned when division by zero results in an error.
)

 

 

Really Thanks for all your support.  PowerPivot is new for me, and I want to learn a lot.  Blessings!

Yes!!!! Awesome!  Thx for all your answers, and you patience.

 

Can you explain a little how this is work? Really helpful.  Blessings!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.