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

Subtotal de una medida

Tengo un conjunto muy complejo de medidas que termina en una mesa. El subtotal de la medida no se suma porque está tomando el porcentaje medio de una medida y multiplicándolo por el total de otra medida en lugar de sumar los valores de cada fila. He intentado usar el método con SUMMARIZE, HASONEVALUE y SUMX, y todavía no funciona. Es probable que se deba al hecho de que las submedidas están reasignando buckets a otros buckets (Mapa de clase de orden de trabajo) y realizando funciones basadas en bucket y región (región grande). La medida que quiero subtotal correctamente se llama "VTB $". ¿Alguien puede analizar las siguientes medidas y ayudar?

VTB $ á [PY Attain %] - [Attain to Bud %]) * [Adj Bud $]

PY Attain % [Adj PY $ TODAY]/[Adj PY $]

Alcanzar a Bud % [Adj Act $] / [Adj Bud $]

Adj Bud $ ? CALCULATE(SUM('Budget Data'[Sum of Native Cost Consolidated (USD)]),FILTER('WorkOrderClassMap','WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")) + [Adj Bud Amt]

Adj PY $ HOY ?

var MinDate á CALCULATE(MIN(_dtDateDim[Date]),ALLSELECTED('_dtDateDim'))

devolución

CALCULATE([Adj Act $],SAMEPERIODLASTYEAR(DATESBETWEEN('_dtDateDim'[Date],MinDate,TODAY())))

Adj PY $ - CALCULATE([Adj Act $],SAMEPERIODLASTYEAR('_dtDateDim'[Fecha]))

Adj Act $ ? CALCULATE([Act $],FILTER('Finance Data','Finance Data'[AccountCodeBizOpsMapping] <> "M&R - Other")) + [Adj Amt]

Adj Bud Amt ? CALCULATE(SUM('Budget Data'[Sum of Native Cost Consolidated (USD)]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] ?

Desglose de presupuestos ( Budget Breakout)

var NormalBreakout á DIVIDE(CALCULATE(DISTINCTCOUNT('Work Order Breakout (Closed)'[WorkOrder_Name]),FILTER('Work Order Breakout (Closed)','Work Order Breakout (Closed)'[WorkOrder_Class] <> "M&R - Other")),CALCULATE(DISTINCTCOUNT(' Work Order Breakout (Closed)'[WorkOrder_Name]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")))

var HQOtherBreakout á DIVIDE(CALCULATE(SUM('Budget Data'[Sum of Native Cost Consolidated (USD)]),FILTER('WorkOrderClassMap','WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")),CALCULATE(SUM('Budget Data'[Sum of Native Cost Cost Map] <> "M&R - Other")),CALCULATE(SUM('Budget Data'[Sum of Native Cost Cost Map] <> "M&R - Other")),CALCULATE(SUM(SUM('Budget Data'[Sum of Native Cost Cost Map'[Sum of Native Cost Cost Map] <> "M&R - Other")),CALCULATE(SUM(SUM('Budget Data'[Sum of Native Cost Cost Map'[Sum of Native Cost Cost Map] <> "M&R - Other")),CALCULATE(SUM(SUM('Budget Data'[Sum of Native Cost Cost Map'[Sum of Native Cost Consolidado (USD)]),FILTER(ALL('WorkOrderClassMap'[Mapa de clases de orden de trabajo]),'WorkOrderClassMap'[Mapa de clase de orden de trabajo] <> "M&R - Otro")))

devolución

IF(MAX('Finance Region Mapping'[Big Region]) á "HQ/Other",HQOtherBreakout,NormalBreakout)

Adj Amt á CALCULATE(SUM('Finance Data'[InvoiceAmountUSD]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] á "M&R - Other"))*[Breakout %]

Breakout % á DIVIDE(CALCULATE(DISTINCTCOUNT('Work Order Breakout (Closed)'[WorkOrder_Name]),FILTER('Work Order Breakout (Closed)','Work Order Breakout (Closed)'[WorkOrder_Class] <> "M&R - Other")),CALCULATE(DISTINCTCOUNT('Work Order Order Order Breakout (Closed)'[WorkOrder_Name]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")))

Acto $ - SUM('Datos financieros'[FacturaAmountUSD])

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Subtotal de una medida

Intente mirar esto:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

Usted debe ser capaz de utilizar esta técnica para tener cálculos completamente diferentes en cualquier nivel.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Subtotal de una medida

Hola , @glove80

Los datos de muestra y el resultado esperado nos facilitarán la comprensión y la resolución de su problema.

O,puede tomar una captura de pantalla de su tabla visual para que podamos entender los campos y medidas que pone en.

Saludos
Equipo de Apoyo Comunitario _ Eason

Highlighted
Microsoft
Microsoft

Re: Subtotal de una medida

Yo mismo encontré la solución. Usé GROUPBY en lugar de SUMMARIZE para conservar algunos de los filtros que había pasado en las medidas que componen [VTB $]. Hice esto:

Adj VTB $ á IF(HASONEVALUE('WorkOrderClassMap'[Work Order Class Map]),[VTB $],SUMX(GROUPBY('WorkOrderClassMap','WorkOrderClassMap'[Work Order Class Map]),[VTB $]))
y luego lo pasó a esto:
Adj VTB $ Fijo ?
var SummarizedTableRegion á SUMMARIZE('Finance Region Mapping','Finance Region Mapping'[Big Region],"RegionBreakout",[Adj VTB $])
devolución
IF(HASONEVALUE('Finance Region Mapping'[Big Region]),[Adj VTB $],SUMX(SummarizedTableRegion,[RegionBreakout]))

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021