Problema con Medida que presenta datos a partir del resultado final de otra

[fsanchez79]

Buen día a Todos, primero me presente mi nombre es Antonio Sanchez y soy un poco nuevo en power bi, les comentare el problema que me encontrado al tratar de hacer un modelo y sería genial si alguien me puede ayudar o guiarme como puedo solucionarlo

en la data tengo varios proveedores, a los cuales debido a problemas con los productos que nos envian se les realizan cargos, pero para lograr determinar el monto a recuperar necesito primero que la medida verifique que si la suma total de lo recuperado es mayor a la responsabilidad actual del proveedor entonces me coloque 0 en cada linea, pero si por el contrario es menor entonces haga la resta  linea a linea

he creado la siguiente medida y si bien funciona linea a linea, no realiza la parte de verificar la suma total y verificar lo descrito arriba

PDR =

        IF(SUMX(comprasoverconsumtion,comprasoverconsumtion[Costo Textilera])>[$ RT (Sc Actual)],0,

                 IF([$ RT (Sc Actual)]<=0,0,

                                 [$ RT (Sc Actual)]- SUMX(comprasoverconsumtion,comprasoverconsumtion[Costo Textilera])))

Y luego esta medida la uso en esta otra

$ Pdte de Recuperar =

                  Var _Text=SUMMARIZE(BaseText2,BaseText2[Color],BaseText2[ProveedorReal],"_Valor", [PDR])

                                  return IF(HASONEVALUE(BaseText2[Color]),

                                            [PDR],SUMX(_Text,[_Valor]))

anexo ejemplos, 

ProveedorReal	Componente Asignado	Color	$ Resp. Text	Recuperado	$ Pdte de Recuperar
Proveedor ABC	JERSEY	WHITE	$            3,500	$                             -	$                                     3,500
Proveedor ABC	JERSEY	BLACK	$         408.38	$         328.00	$                                     75.38
Proveedor ABC	JERSEY	GREY	$         1,000	$         4,345.33	$                                                      -
Proveedor ABC	JERSEY	HAWAIIAN	$            880	$         1,327	$                                                      -
			$         4,888.38	$         6,000.33	$                                     3,575.38
Proveedor XYZ	JERSEY	WHITE	$            600.00	$            500.00	$                                     100.00
Proveedor XYZ	JERSEY	PINK	$         1,700.00	$            500.00	$                                  1,200.00
			$         2,300	$         1,000.00	$                                  1,300.00

 aqui la medida si hace la resta linea a linea y el resultado se ve en la columna $ pdte de recuperar, pero el problema es que en el proveedor ABC la suma de lo recuperado es mayor a la responsabilidad actual, por lo tanto en la columna $ pdte de recuperar no debería de presentar datos

anexo detalle de lo que espero que me devuelva la medida, espero haberme esplicado y que alguien me pueda guiar a solucionar este problema, agradezco desde ya su valiosa ayuda

ProveedorReal	Componente Asignado	Color	$ Resp. Text	Recuperado	$ Pdte de Recuperar
Proveedor ABC	JERSEY	WHITE	$  3,500	$            -	$                        -
Proveedor ABC	JERSEY	BLACK	$  408.38	$  328.00	$                        -
Proveedor ABC	JERSEY	GREY	$ 1,000	$ 4,345.33	$                        -
Proveedor ABC	JERSEY	HAWAIIAN	$  880	$ 1,327	$                        -
			$ 4,888.38	$ 6,000.33	$                        -
Proveedor XYZ	JERSEY	WHITE	$  600.00	$  500.00	$               100.00
Proveedor XYZ	JERSEY	PINK	$ 1,700.00	$  500.00	$             1,200.00
			$ 2,300.00	$  1,000.00	$             1,300.00

[fsanchez79 · Accepted solution]

Hello Evelyn9, thank you for your valuable help, the results you present are what I expect, only if it helps me to make the formula present the final total, since I currently do not present it, I tried to do it but I did not get it to work

[v-eqin-msft · Accepted solution]

Hi @fsanchez79 ,

I have modified my measures:

Measure 2 = 
var _diff=CALCULATE(SUM('Table'[ $ Resp. Text ])-SUM('Table'[ Recovered ]),ALLEXCEPT('Table','Table'[Real Provider],'Table'[Colour]))
return IF(_diff <0,0,_diff)

Measure 3 = IF( MINX(FILTER(ALL('Table'),'Table'[Real Provider]=MAX('Table'[Real Provider])),[Measure 2])=0,0,[Measure 2])

Measure 4 = IF(ISINSCOPE('Table'[Real Provider]),[Measure 3], SUMX('Table',[Measure 3]))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fsanchez79]

Thank you for your help, it is just what I was looking for, I will close this query, happy day

[v-eqin-msft]

Hi @fsanchez79 ,

Sorry for my misunderstanding,please try this:

Measure 2 = 
var _diff=CALCULATE(SUM('Table'[ $ Resp. Text ])-SUM('Table'[ Recovered ]),ALLEXCEPT('Table','Table'[Real Provider],'Table'[Colour]))
return IF(_diff <0,0,_diff)

Measure 3 = IF(ISINSCOPE('Table'[Real Provider]),IF( MINX(FILTER(ALL('Table'),'Table'[Real Provider]=MAX('Table'[Real Provider])),[Measure 2])=0,0,[Measure 2]))

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fsanchez79 · Accepted solution]

Hello Evelyn9, thank you for your valuable help, the results you present are what I expect, only if it helps me to make the formula present the final total, since I currently do not present it, I tried to do it but I did not get it to work

[v-eqin-msft]

Hi @fsanchez79 , 

According to my understanding, you want to get the correct sub-total based on a measure,please try this:

Measure = IF(HASONEVALUE('Table'[Colour]),IF(MAX('Table'[ $ Resp. Text ])<MAX('Table'[ Recovered ]),0,MAX('Table'[ $ Resp. Text ])-MAX('Table'[ Recovered ])))

Recover $ Pdte = SUMX('Table',[Measure])

Or create a column instead:

Column = 
var _diff= [ $ Resp. Text ]-[ Recovered ]
return IF(_diff<=0,0,_diff)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fsanchez79]

thanks for your reply, but unfortunately not the solution to my problem, what I need is that the measure when verifying that the recovered is greater than the responsibility, do not calculate the subtraction line by line, but place zero and the data that returns both possibilities that it has given me return data

In the example you put for supplier ABC the result should be zero, since the recovered is greater than the responsibility, while for the supplier XYZ it must generate the subtraction since the recovered is less than the responsibility

[AlB]

Hi @fsanchez79 

Can you share the pbix?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.