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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
powerbi2srm
Resolver II
Resolver II

Calcular medida de totales

Hola!

 

Necesito ayuda para crear una medida que me calcule totales de tal forma que cuando arrastre esa medida a una tabla no le afecten los filtros de cada fila.

 

Para explicarlo mejor os pongo un ejemplo. Una empresa vende dos productos (mesas y sillas) a dos consumidores: 

 

table1.jpg

Lo que busco es una medida que me calculase el total para cada producto (8 sillas vendidas y 3 mesas), independientemente de cuál sea la factura o el cliente:

 

table2.jpg

 

Muchas gracias!!

2 ACCEPTED SOLUTIONS

@powerbi2srm 

 

total unit by product measure =
  VAR __product = MAX('Table 2'[product])
  VAR __table = FILTER(ALL('Table 1'),[product] = __product)
RETURN
  SUMX(__table,[units by invoice])

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@powerbi2srm El archivo PBIX se adjunta debajo de la firma

Measure = 
    VAR __Product = MAX('dim_product'[product_id])
    VAR __Table = FILTER(ALL('fact_sale_invoice'),[product_id] = __Product)
RETURN
    IF(SUM('fact_sale_invoice'[product_quantity])>0,SUMX(__Table,[product_quantity]),BLANK())

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

13 REPLIES 13
powerbi2srm
Resolver II
Resolver II

Muchas gracias @Greg_Deckler. Esto me sirvió para resolver el problema, sin embargo al trabajar con grandes cantidades de datos daba algunos errores de carga. Probé esta cosa que me funcionó perfecto. Lo pongo por aquí. Por si alguien tiene el mismo problema en un futuro:

 

total unit by product = 
VAR TblSummary = ADDCOLUMNS(
    VALUES(fact_sale_invoice[product_id]),
    "Total", CALCULATE(SUM(fact_Sale_invoice[product_quantity]),ALL(fact_sale_invoice),VALUES(fact_Sale_invoice[product_id]))
)
RETURN
    SUMX(TblSummary,[Total])

 

powerbi2srm
Resolver II
Resolver II

Sigue sin funcionar... No entiendo por qué @Greg_Deckler 

@powerbi2srm ¿Puede publicar su archivo PBIX o una muestra que replica el modelo de datos?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Sí, pero ¿cómo puedo pasarte el archivo? @Greg_Deckler 

@powerbi2srm Usa OneDrive o Google Drive o Box, cualquier servicio de carga de archivos.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@powerbi2srm El archivo PBIX se adjunta debajo de la firma

Measure = 
    VAR __Product = MAX('dim_product'[product_id])
    VAR __Table = FILTER(ALL('fact_sale_invoice'),[product_id] = __Product)
RETURN
    IF(SUM('fact_sale_invoice'[product_quantity])>0,SUMX(__Table,[product_quantity]),BLANK())

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Disculpa las molestias, pero ¿como tendría que hacer para filtrar esos datos por fecha a través de DAX? Es decir, por ejemplo:  esa medida para el mes de enero. Esto es lo que he intentado, pero no me lo reconoce:

Unidades_totales_enero = 
VAR __Product = MAX(dim_product[product_id])

VAR __Table =
FILTER(
    ALLEXCEPT(fact_sale_invoice_line, fact_sale_invoice_line[invoice_date_id]),
    [product_id] = __Product
)

var resultado =
IF(
    SUM(fact_sale_invoice_line[product_quantity])>0,
    CALCULATE(
        SUMX(__Table, fact_sale_invoice_line[product_quantity]),
        AND(
            fact_sale_invoice_line[invoice_date_id]>=20220201,
            fact_sale_invoice_line[invoice_date_id]<=20220228
        )
    ),
    BLANK()
)

return resultado

 

Perdón por las molestias, @Greg_Deckler , pero es que me urge.

@powerbi2srm Maybe:

Measure = 
    VAR __Product = MAX('dim_product'[product_id])
    VAR __Table = FILTER(ALL('fact_sale_invoice_line'),[product_id] = __Product && [invoice_date_id]>=20220201 && [invoice_date_id]<=20220228
        )
RETURN
    IF(SUM('fact_sale_invoice_line'[product_quantity])>0,SUMX(__Table,[product_quantity]),BLANK())

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Mil gracias!!!! 😀

Greg_Deckler
Super User
Super User

@powerbi2srm 

 

total unit by product column =
  VAR __product = [product]
  VAR __table = FILTER('Table'[product] = __product)
RETURN
  SUMX(__table,[units by invoice])

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Disculpa, @Greg_Deckler, las columnas de la tabla que puse pertenecen a distintas tablas relacionadas. Por lo que creo que tu solución no funciona... Necesito una medida que me calcule eso para poder arrastrarla a la visualización de tabla.

@powerbi2srm 

 

total unit by product measure =
  VAR __product = MAX('Table 2'[product])
  VAR __table = FILTER(ALL('Table 1'),[product] = __product)
RETURN
  SUMX(__table,[units by invoice])

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors