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
Anonymous
Not applicable

sumar si conjunto en power BI

Hola,

 

Lo que deseo hacer es una consulta similar al (sumar.si.conjunto) de excel de forma anidada, sumando cada (sumar.si.conjunto) como se muestra en la imagen de excel. La peculiaridad de mi ejemplo, es que en exel, completan los datos comparando la fecha y 3 tipos de codigo de tecnico de la hoja actividades realizadas, al final se suman las coincidencias y luego se obtiene el valor final. por ejemplo: 

 

aqui, se compara la fecha y el tecnico contra la hoja de actividades realizadas(siguiente imagen), para obtener la cantidad de eventos asignados y cerrados

Sin título.png
de aqui se toman los datos, y mediante al (sumar.si.conjunto) se compara el tecnico de la primera imagen vs el tecnico de esta segunda imagen(son 3, por eso es una consulta anidada), si todos los datos al compararse son iguales, se suman sus valores de asignado, peso, peso asignado y peso cerrado, tenido el valor final en la imagen o tabla superior
actrealizadas.png


adjunto imagen de power BI, donde tengo la segunda tabla cargada, pero no se como aplicar el sumar.si.conjunto para obtener los valores finales.

power bi.png
Espero muy respetuosamente me puedan ayudar

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hello @mmontoyah ,

In Power BI, the IF part of this function is created using the CALCULATED function, where you can add multiple filters so that you need a formula similar to this:

Value =
CALCULATE (
    SUM ( Table[Column] ),
    Table[Column2] = 10,
    Table[Column3] <= MAX ( Table[Column3] )
)

We need to adjust this to your example, can you share the information in a paste copy format or a mockup of your file?

See this post on how to get answers to your question quickly (courtesy of @Greg_Deckler)and How to provide sample data in the Power BI forum (courtesy of @ImkeF).


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hi @Anonymous ,

 

I have a couple of questions so that I can give you the correct answer:

  • The Act Realizadas on the excel spreasheet correspond to the C Entrega on Power BI?
  • To what I can see you are summing the value of 3 different technicians on the sumif correct?
  • What are the columns where the Techinicians on PBI are the same on Excel that you use for the 3 different SUMIF?
  • What is the table that you use has you techincian base? 
    • Detalle_encargados2 or Detalle_ayudantes1 or Lista tec Entrega or Lista tec ayudantes Entrega

Basically looking at the data if you will not need to do a SUMIF because the relationship between your tables (calendar, C Entrega, techincians) will get you the correct result the maximum that you may need is to create a inactive relationship for the other 2 columns and use a syntax of USERELATIONSHIP.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hello @mmontoyah ,

In Power BI, the IF part of this function is created using the CALCULATED function, where you can add multiple filters so that you need a formula similar to this:

Value =
CALCULATE (
    SUM ( Table[Column] ),
    Table[Column2] = 10,
    Table[Column3] <= MAX ( Table[Column3] )
)

We need to adjust this to your example, can you share the information in a paste copy format or a mockup of your file?

See this post on how to get answers to your question quickly (courtesy of @Greg_Deckler)and How to provide sample data in the Power BI forum (courtesy of @ImkeF).


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

tienes algun correo electronico para enviar los archivos. Por este medio no se como adjuntarlos

Hi @Anonymous ,

 

You can send the files trough onedrive, google drive, wetransfer or similar links, if you don't want to post them here please send it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @Anonymous ,

 

I have a couple of questions so that I can give you the correct answer:

  • The Act Realizadas on the excel spreasheet correspond to the C Entrega on Power BI?
  • To what I can see you are summing the value of 3 different technicians on the sumif correct?
  • What are the columns where the Techinicians on PBI are the same on Excel that you use for the 3 different SUMIF?
  • What is the table that you use has you techincian base? 
    • Detalle_encargados2 or Detalle_ayudantes1 or Lista tec Entrega or Lista tec ayudantes Entrega

Basically looking at the data if you will not need to do a SUMIF because the relationship between your tables (calendar, C Entrega, techincians) will get you the correct result the maximum that you may need is to create a inactive relationship for the other 2 columns and use a syntax of USERELATIONSHIP.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

  • ¿La Ley Realizadas en la hoja de Excel correspondiente a la C Entrega en Power BI?

Correcto. 

 

  • ¿A lo que puedo ver está sumando el valor de 3 técnicos diferentes en el sumif correcto?

Estoy sumando los valores de 3 tecnicos diferentes, ya puede ser que existan dos tecnicos realizando el mismo trabajo en una fecha especifica, pero que tambien realicen trabajos separados en la misma fecha, por lo tanto se deben de sumar los valores individualmente para la fecha correspondiente

 

  • ¿Cuáles son las columnas donde los techinicians en PBI son los mismos en Excel que se usan para los 3 SUMIF diferentes?

Tecnico1, tecnico 2, tecnico 3

 

  • ¿Cuál es la tabla que usas tiene tu base techincian?
    • Detalle_encargados2 o Detalle_ayudantes1 o Lista tec Entrega o Lista tec ayudantes Entregas

lista tec entrega

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.