simplify measure IF ( query has exceeded the available resources.)

[admera]

Hii All

I have tried this measure and it has worked in desktop, but this measure is very heavy and fails when displayed in the power bi service with the notification " query has exceeded the available resources". can you rewrite this measure so that it is lighter?
Any help would be appreciated!

description
TTNT A, B, C, D, E is total sales by brand
TA, B, C, D, E is total target by brand

Measure 1
Cek All =
var a = IF(AND([TTNT A]/[TA Target]>=0.25,[TTNT A]/[TA Target]<1),[TA Target]-[TTNT A],0)
var b = IF(AND([TTNT B]/[TB Target]>=0.25,[TTNT B]/[TB Target]<1),[TB Target]-[TTNT B],0)
var c = IF(AND([TTNT C]/[TC Target]>=0.25,[TTNT C]/[TC Target]<1),[TC Target] -[TTNT C],0)
var d = IF(AND([TTNT D]/[TD Target]>=0.25,[TTNT D]/[TD Target]<1),[TD Target]-[TTNT D],0)
var e = IF(AND([TTNT E]/[TE Target]>=0.25,[TTNT E]/[TE Target]<1),[TE Target]-[TTNT E],0)
RETURN
a+b+c+d+e

Measure 2 (for total)

Potential Sales = SUMX(VALUES(master_dealer[dealer_code),[Cek All])

[Kedar_Pande]

@admera 

Optimized Measures:

Cek All = 
SUMX(
{ 
("A", [TTNT A], [TA Target]),
("B", [TTNT B], [TB Target]),
("C", [TTNT C], [TC Target]),
("D", [TTNT D], [TD Target]),
("E", [TTNT E], [TE Target])
},
VAR Sales = SELECTEDVALUE(Value1)
VAR Target = SELECTEDVALUE(Value2)
RETURN IF(AND(Sales / Target >= 0.25, Sales / Target < 1), Target - Sales, 0)
)

Potential Sales = SUMX(VALUES(master_dealer[dealer_code]), [Cek All])

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[rajendraongole1]

Hi @admera - Instead of calculating each brand’s values separately and then summing them, we can use a SWITCH approach with a summarized table to reduce the workload.

Below is the simplified one

Cek All Optimized =
SUMX(
{
( "A", [TTNT A], [TA Target] ),
( "B", [TTNT B], [TB Target] ),
( "C", [TTNT C], [TC Target] ),
( "D", [TTNT D], [TD Target] ),
( "E", [TTNT E], [TE Target] )
},
VAR TTNT_Value = SELECTEDVALUE([Value1])
VAR Target_Value = SELECTEDVALUE([Value2])
RETURN IF(
AND(TTNT_Value / Target_Value >= 0.25, TTNT_Value / Target_Value < 1),
Target_Value - TTNT_Value,
0
)
)

potential sales measure as below

Potential Sales Optimized =
SUMX(
VALUES(master_dealer[dealer_code]),
[Cek All Optimized]
)

Hope this helps in your scenerio

[admera]

thanks for your solution Raj, but when I implement measure the result is 0. maybe the measure are incomplete?

what is the content of the selectvalue function? is that column for total sales & total target right? 
VAR TTNT_Value = SELECTEDVALUE(Total no Tax) 
VAR Target_Value = SELECTEDVALUE([Target])

[Anonymous]

Hi @admera ,

Are [Total no Tax] and [Target] the fact field in your table? And is there any slicers for these fields? Could you please

provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

If your table have the similar structure with the below one, you can follow the steps in the attached pbix file to get it.

Category	TTNT	Target
A	204	200
B	280	285
C	121	140
D	269	270
E	232	240

Best Regards