Hello everyone
I am trying to generate a measure such that it tells me the number of materials that make up the 80% of sales given a segment for all customers, then when filter by customer I indicate how many materials that customer has assigned are within the 8020 materials previously calculated. I have found a solution but the measurement takes a long time to calculate, so I try to optimize it. The measure is:
Measure =
VAR Amount Earned = CALCULATE(Measures[Measure Amount Earned], ALLSELECTED(Billing[Segment]),ALLSELECTED(Billing[Customer]))
VAR Table1 = CALCULATETABLE(FILTER(VALUES(Facturacion[Material]),DIVIDE(SUMX(TOPN(Measures[Measure Rank],CALCULATETABLE(VALUES(Facturacion[Material]),ALLSELECTED(Facturacion[Material])),[Measure Amount Earned]),Measures[Measure Amount Earned]),IF(NOT(ISBLANK(AmountEarned)),CALCULATE([Measure Amount Earned],ALLSELECTED(Invoice[Material]))))<0.8),ALL(Billing[Customer]))
VAR MedidaNumeroMateriales8020_Total = COUNTROWS(Table 1)
VAR Table2 = FILTER(CALCULATETABLE(FILTER(VALUES(Facturacion[Material]),DIVIDE(SUMX(TOPN(Measures[Measure Rank],CALCULATETABLE(VALUES(Facturacion[Material]),ALLSELECTED(Facturacion[Material])),[Measure Amount Earned]),Measures[Measure Amount Earned]),IF(NOT(ISBLANK(AmountEarned)),CALCULATE([Measure Amount Earned],ALLSELECTED(Invoice[Material]))))<0.8),ALL(Billing[Customer]),Measures[Measure Amount Earned]>0)
VAR MedidaNumeroMateriales8020_Cliente = COUNTROWS(Table2)
RETURN
DIVIDE(MedidaNumeroMateriales8020_Cliente,MedidaNumeroMateriales8020_Total)
where we only take a measure called Rank Measure that was calculated previously defined as:
Measure Rank = IF(HASONEVALUE(Billing[Material]),RANKX(ALL(Billing[Material]),Measures[Measure Amount Earned]),BLANK())
as I optimize the measure since when inegrar everything, that is, placing the rank measure within the measure of the calculation gives me different values or if there is any different way to achieve that the measure was calculated faster
