Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi to all!
I have this table into Excel:
Titulo | Mes | Item | valor |
Libros | Enero | Libro 1 | 30 |
Libros | Enero | Libro 2 | 50 |
Libros | Enero | Libro 3 | 20 |
Libros | Enero | Libro 4 | 50 |
Libros | Enero | Libro 5 | 40 |
Libros | Enero | Libro 6 | 20 |
Video Juegos | Enero | Video Juego 1 | 70 |
Video Juegos | Enero | Video Juego 2 | 50 |
Video Juegos | Enero | Video Juego 3 | 40 |
Musica | Enero | CD1 | 15 |
Musica | Enero | CD2 | 10 |
Musica | Enero | CD3 | 20 |
Musica | Enero | CD4 | 50 |
Libros | Febrero | Libro 1 | 10 |
Libros | Febrero | Libro 2 | 15 |
Libros | Febrero | Libro 3 | 20 |
Libros | Febrero | Libro 4 | 25 |
Libros | Febrero | Libro 5 | 32 |
Libros | Febrero | Libro 6 | 20 |
Video Juegos | Febrero | Video Juego 1 | 70 |
Video Juegos | Febrero | Video Juego 2 | 50 |
Video Juegos | Febrero | Video Juego 3 | 40 |
Musica | Febrero | CD1 | 15 |
Musica | Febrero | CD2 | 10 |
Musica | Febrero | CD3 | 20 |
Musica | Febrero | CD4 | 50 |
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:
Any ideas? Thx in advance for your answers.
Solved! Go to Solution.
@JohnJairoV Sorry I totally misread what you were asking for.
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 )
@JohnJairoV This shoud work...
Measure = DIVIDE ( SUM ( Table1[valor] ); CALCULATE ( SUM ( Table1[valor] ); ALL ( Table1[Item] ) ); 0 )
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 in Enero (January):
Pivot Table with Febrero (February) Filter:
@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
@JohnJairoV Okay I think I got it in PBI too!
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 ) )
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!
@JohnJairoV Sorry I totally misread what you were asking for.
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 )
% 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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |