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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.