Need Help with DAX Formula

[KRISH80]

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)

[HarsimranPWRBI]

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 · Accepted solution]

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
)

[KRISH80]

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

[Anonymous]

@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

[KRISH80]

ACV BKG	Close Date	Close Month	Stage
8,994,484	6/28/2019	6/1/2019	Deal Won
8,293,220	8/12/2019	8/1/2019	Deal Won
6,769,000	4/26/2019	4/1/2019	Deal Won
5,600,000	9/25/2019	9/1/2019	Deal Won
4,872,278	6/28/2019	6/1/2019	Deal Won
4,872,278	6/28/2019	6/1/2019	Deal Won
4,660,000	7/30/2019	7/1/2019	Deal Won
4,153,750	10/26/2019	10/1/2019	Deal Won
4,153,750	10/26/2019	10/1/2019	Deal Won
3,675,862	4/10/2019	4/1/2019	Deal Won
3,586,207	4/10/2019	4/1/2019	Deal Won
3,307,980	9/30/2019	9/1/2019	Deal Won
3,100,000	9/25/2019	9/1/2019	Deal Won
3,080,000	7/31/2019	7/1/2019	Deal Won
2,943,701	9/30/2019	9/1/2019	Deal Won
2,800,000	4/30/2019	4/1/2019	Deal Won
2,797,094	9/29/2019	9/1/2019	Deal Won
2,550,000	4/17/2019	4/1/2019	Deal Won
2,500,000	4/17/2019	4/1/2019	Deal Won
2,500,000	4/17/2019	4/1/2019	Deal Won
2,400,000	4/17/2019	4/1/2019	Deal Won
2,400,000	4/17/2019	4/1/2019	Deal Won
2,313,993	4/17/2019	4/1/2019	Deal Won
2,100,000	4/17/2019	4/1/2019	Deal Won
2,000,000	4/17/2019	4/1/2019	Deal Won
2,000,000	4/17/2019	4/1/2019	Deal Won
1,713,780	6/22/2019	6/1/2019	Deal Won
1,500,000	5/31/2019	5/1/2019	Deal Won
1,468,128	7/30/2019	7/1/2019	Deal Won
1,350,000	9/13/2019	9/1/2019	Deal Won
1,311,500	4/23/2019	4/1/2019	Deal Won
1,219,316	7/26/2019	7/1/2019	Deal Won
1,213,993	4/17/2019	4/1/2019	Deal Won
1,208,346	4/26/2019	4/1/2019	Deal Won
1,178,621	5/1/2019	5/1/2019	Deal Won
1,100,000	8/30/2019	8/1/2019	Deal Won
1,088,000	9/13/2019	9/1/2019	Deal Won
1,012,701	7/5/2019	7/1/2019	Deal Won
997,728	5/31/2019	5/1/2019	Deal Won
942,070	10/11/2019	10/1/2019	Deal Won
920,000	9/13/2019	9/1/2019	Deal Won
900,000	11/6/2019	11/1/2019	Deal Won
864,232	6/24/2019	6/1/2019	Deal Won
830,200	5/2/2019	5/1/2019	Deal Won
800,000	4/11/2019	4/1/2019	Deal Won
778,156	7/13/2019	7/1/2019	Deal Won
773,388	7/30/2019	7/1/2019	Deal Won
733,925	7/15/2019	7/1/2019	Deal Won
728,000	8/23/2019	8/1/2019	Deal Won
718,000	4/26/2019	4/1/2019	Deal Won
690,000	7/20/2019	7/1/2019	Deal Won
652,000	5/30/2019	5/1/2019	Deal Won
645,982	6/28/2019	6/1/2019	Deal Won
638,006	10/18/2019	10/1/2019	Deal Won
630,443	7/26/2019	7/1/2019	Deal Won
630,000	6/21/2019	6/1/2019	Deal Won
621,000	5/25/2019	5/1/2019	Deal Won
600,700	7/26/2019	7/1/2019	Deal Won
600,000	8/9/2019	8/1/2019	Deal Won
599,591	7/29/2019	7/1/2019	Deal Won
596,914	5/31/2019	5/1/2019	Deal Won
580,000	9/11/2019	9/1/2019	Deal Won
570,000	5/31/2019	5/1/2019	Deal Won
550,000	5/15/2019	5/1/2019	Deal Won
537,578	7/12/2019	7/1/2019	Deal Won
529,788	10/25/2019	10/1/2019	Deal Won
524,000	6/6/2019	6/1/2019	Deal Won
518,000	7/1/2019	7/1/2019	Deal Won