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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KRISH80
Helper II
Helper II

Need Help with DAX Formula

I need to Calculate Ratio of our WINS where the manufacturer is not equal CISCO and certain other filtering to be applied. The formula is not giving me any error but it is not showing me the result. It throws "See Details Error". Kindly help.

 

GS Ratio = DIVIDE(CALCULATE( (SUM('Closed Opprtunities'[TS])+SUM('Closed Opprtunities'[SS])+SUM('Closed Opprtunities'[MS])+ SUM('Closed Opprtunities'[TRNG])+SUM('Closed Opprtunities'[CS])),FILTER('Closed Opprtunities', 'Closed Opprtunities'[OEM]<>"CISCO" &&'Closed Opprtunities'[Attach]<=0 &&'Closed Opprtunities'[SNT]<=0 && 'Closed Opprtunities'[VBR]<=0)),[TOTAL-WINS],0)
1 ACCEPTED SOLUTION
Anonymous
Not applicable

GS Ratio =
DIVIDE (
    CALCULATE (
        SUMX (
            'Closed Opprtunities',
            'Closed Opprtunities'[TS] + 'Closed Opprtunities'[SS] + 'Closed Opprtunities'[MS] + 'Closed Opprtunities'[TRNG] + 'Closed Opprtunities'[CS]
        ),
        FILTER (
            'Closed Opprtunities',
            'Closed Opprtunities'[OEM] <> "CISCO"
                && 'Closed Opprtunities'[Attach] <= 0
                && 'Closed Opprtunities'[SNT] <= 0
                && 'Closed Opprtunities'[VBR] <= 0
        )
    ),
    [TOTAL-WINS],
    0
)

View solution in original post

5 REPLIES 5

Question : IS [TOTAL-WINS] is a measure? if no then use some aggregation on same

Also if this does not answer your question please provide sample data for Power bi model for same and include some screenshots too.

 

Thanks,

Harry

Anonymous
Not applicable

GS Ratio =
DIVIDE (
    CALCULATE (
        SUMX (
            'Closed Opprtunities',
            'Closed Opprtunities'[TS] + 'Closed Opprtunities'[SS] + 'Closed Opprtunities'[MS] + 'Closed Opprtunities'[TRNG] + 'Closed Opprtunities'[CS]
        ),
        FILTER (
            'Closed Opprtunities',
            'Closed Opprtunities'[OEM] <> "CISCO"
                && 'Closed Opprtunities'[Attach] <= 0
                && 'Closed Opprtunities'[SNT] <= 0
                && 'Closed Opprtunities'[VBR] <= 0
        )
    ),
    [TOTAL-WINS],
    0
)

@Anonymousthanks a lot but getting this error. Screen shot attached.

Error2.png

Anonymous
Not applicable

@KRISH80 

can you check data types of columns you have used in filter arguments. Or else share some sample data

Attach, SNT, VBR should be of integer type and OEM should be of type text

Columns used in sumx should be of type integer

ACV BKGClose DateClose MonthStage
8,994,4846/28/20196/1/2019Deal Won
8,293,2208/12/20198/1/2019Deal Won
6,769,0004/26/20194/1/2019Deal Won
5,600,0009/25/20199/1/2019Deal Won
4,872,2786/28/20196/1/2019Deal Won
4,872,2786/28/20196/1/2019Deal Won
4,660,0007/30/20197/1/2019Deal Won
4,153,75010/26/201910/1/2019Deal Won
4,153,75010/26/201910/1/2019Deal Won
3,675,8624/10/20194/1/2019Deal Won
3,586,2074/10/20194/1/2019Deal Won
3,307,9809/30/20199/1/2019Deal Won
3,100,0009/25/20199/1/2019Deal Won
3,080,0007/31/20197/1/2019Deal Won
2,943,7019/30/20199/1/2019Deal Won
2,800,0004/30/20194/1/2019Deal Won
2,797,0949/29/20199/1/2019Deal Won
2,550,0004/17/20194/1/2019Deal Won
2,500,0004/17/20194/1/2019Deal Won
2,500,0004/17/20194/1/2019Deal Won
2,400,0004/17/20194/1/2019Deal Won
2,400,0004/17/20194/1/2019Deal Won
2,313,9934/17/20194/1/2019Deal Won
2,100,0004/17/20194/1/2019Deal Won
2,000,0004/17/20194/1/2019Deal Won
2,000,0004/17/20194/1/2019Deal Won
1,713,7806/22/20196/1/2019Deal Won
1,500,0005/31/20195/1/2019Deal Won
1,468,1287/30/20197/1/2019Deal Won
1,350,0009/13/20199/1/2019Deal Won
1,311,5004/23/20194/1/2019Deal Won
1,219,3167/26/20197/1/2019Deal Won
1,213,9934/17/20194/1/2019Deal Won
1,208,3464/26/20194/1/2019Deal Won
1,178,6215/1/20195/1/2019Deal Won
1,100,0008/30/20198/1/2019Deal Won
1,088,0009/13/20199/1/2019Deal Won
1,012,7017/5/20197/1/2019Deal Won
     997,7285/31/20195/1/2019Deal Won
     942,07010/11/201910/1/2019Deal Won
     920,0009/13/20199/1/2019Deal Won
     900,00011/6/201911/1/2019Deal Won
     864,2326/24/20196/1/2019Deal Won
     830,2005/2/20195/1/2019Deal Won
     800,0004/11/20194/1/2019Deal Won
     778,1567/13/20197/1/2019Deal Won
     773,3887/30/20197/1/2019Deal Won
     733,9257/15/20197/1/2019Deal Won
     728,0008/23/20198/1/2019Deal Won
     718,0004/26/20194/1/2019Deal Won
     690,0007/20/20197/1/2019Deal Won
     652,0005/30/20195/1/2019Deal Won
     645,9826/28/20196/1/2019Deal Won
     638,00610/18/201910/1/2019Deal Won
     630,4437/26/20197/1/2019Deal Won
     630,0006/21/20196/1/2019Deal Won
     621,0005/25/20195/1/2019Deal Won
     600,7007/26/20197/1/2019Deal Won
     600,0008/9/20198/1/2019Deal Won
     599,5917/29/20197/1/2019Deal Won
     596,9145/31/20195/1/2019Deal Won
     580,0009/11/20199/1/2019Deal Won
     570,0005/31/20195/1/2019Deal Won
     550,0005/15/20195/1/2019Deal Won
     537,5787/12/20197/1/2019Deal Won
     529,78810/25/201910/1/2019Deal Won
     524,0006/6/20196/1/2019Deal Won
     518,0007/1/20197/1/2019Deal Won

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors