cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

Accepted Solutions
Highlighted
Super User V
Super User V

Re: add if set in power BI

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

Highlighted
Super User V
Super User V

Re: sumar si conjunto en power BI

Hi @mmontoyah ,

 

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
Highlighted
Super User V
Super User V

Re: add if set in power BI

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

Highlighted
Regular Visitor

Re: sumar si conjunto en power BI

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

Highlighted
Super User V
Super User V

Re: sumar si conjunto en power BI

Hi @mmontoyah ,

 

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





Highlighted
Regular Visitor

Re: sumar si conjunto en power BI

Highlighted
Super User V
Super User V

Re: sumar si conjunto en power BI

Hi @mmontoyah ,

 

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

Highlighted
Regular Visitor

Re: sumar si conjunto en power BI

  • ¿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

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