What am i doing wrong

[RvdHeijden]

Hello,

Im a DAX newbie but have a background in Excell and in Excell i would have figured out this problem in minutes but in DAX it already cost me a couple of days because they way formules are written is very different from in excel.

My problem is as follows.

I want to calculate a % from total 'Orders' (stagename) devided by the SUM of 4 'Stagenames' such as below

i already know this formule is wrong but im not sure what i am doing wrong.

But like i said im a beginner so it's probably something simple (at least i hope so)

[Anonymous]

Sorry... this formula works !

Scoringskans = CALCULATE(DIVIDE(DISTINCTCOUNT(Project[StageName]);COUNTROWS(Project));Project[StageName]="Order" || Project[StageName]="Prospect" || Project[StageName]="Lost Order" || Project[StageName]="Bid Made")

the || represent the OR function

[RvdHeijden]

@Anonymous

It still doesn't work because now it show almost in all cells 100% and some are empty because they have a status <> the ones in the formula.

The meaning was to calculate the total sum of all 'Orders' devided by all the customers I made a bid. So if i have 5 orders but made 100 bids then i have a 5% order rate

In this formula im missing the part where it counts all the rows with the status 'Order'

[Anonymous]

For that you need 2 CALCULATIONS functions in your formula AND not count distinct...

ScoringskansORDERS.vs.BIDSMADE RATIO = DIVIDE(CALCULATE(COUNTROWS(Project[StageName]);Project[StageName] = "Orders"));CALCULATE(COUNTROWS(Project));Project[StageName]= Project[StageName]="Bid Made")

[RvdHeijden]

@Anonymous

I think we are almost so thanks for your time and help but there im missing a couple of values in the formula.

ScoringsKans2 = DIVIDE(CALCULATE(COUNTROWS(Project[Stagename]);Project[Stagename] = "order";CALCULATE(COUNTROWS(Project));Project[StageName]"Lost Order"||Project[StageName]="Bid made"||Project[StageName]="Order"||Project[StageName]="Prospect")

Simply put i want to calculate #Orders / (SUM(#Lost Orders+#Bid made+#Order+#Prospect)

[Anonymous · Accepted solution]

Scoringskans =
DIVIDE (
    CALCULATE ( COUNTROWS ( Project ); Project[StageName] = "Order" );
    CALCULATE (
        COUNTROWS ( Project );
        Project[StageName] = "Order"
            || Project[StageName] = "Lost Order"
            || Project[StageName] = "Bid Made"
            || Project[StageName] = "Prospect"
    )
)

Take note that in my example I have 1 line for "Other" which I don't include in my formula so I have 6/12 = 0,5 which is correct.

[RvdHeijden]

@Anonymous

Thank you, this works and it reads a 40,58 % change on an order

[RvdHeijden]

@Anonymous

Correction because im not sure if im missing that column. it's just that i have only 2 values now....it's either 100% or BLANK

[Anonymous]

Had similar problems when going from Excel to DAX. As soon as you figure out the CALCULATE function you're like a newly set free bird from a cage.

your measure = CALCULATE ( DIVIDE ( DISTINCTCOUNT ( 'tableNAME'[tableCOLUMN] ) ; COUNTROWS ( 'tableNAME' ) ) ; Project[StageName] = "Order" ; Project[StageName] = "YOUR CRITERIA(S)" )

Bon apetit

[RvdHeijden]

How do u set the different kinds of criteria's ? are the devided bij ; or , ?

Scoringskans = CALCULATE( Divide (Distinctcount (Project[StageName]) ; countrows (Project));Project[StageName] = "Order";Project[StageName]="Lost Order" && "Bid Made" && "Order" && "Prospect")))))

This way doesn't seem to work or i made a mistake somewhere

[Anonymous]

How many unique identities do you have in StageName column ?

If they are for example 5, then you just have to exclude the one you don't want in your calculation with

Project[StageName] <> "ID YOU DONT WANT"

Otherwise... for your 4 filtered ID's...

This should work

Scoringskans = CALCULATE( Divide (DISTINCTCOUNT(Project[StageName]) ; COUNTROWS (Project));Project[StageName] = "Order";Project[StageName]="Lost Order" ; Project[StageName]="Bid Made" ; Project[StageName]= "Order" ;Project[StageName]= "Prospect")

[Bokazoit]

CALCULATE is a multi-parameter function. You need to say

=CALCULATE(DISTINCT(Project[Stagename]);Project[Stagename] = "ORDER")/(CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="xxx")+CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="xxx")+CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="xxx")+CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="xxx"))

[RvdHeijden]

@Bokazoit

Apparently your formule doesn't work either however there is a bigger chance that i did something wrong.

Can you tell me what it is ?

ScoringsKans2 = CALCULATE(DISTINCT(Project[Stagename]);Project[Stagename] = "ORDER")/(CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="Lost Order")+CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="Order")+CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="Prospect")+CALCULATE(COUNT(Project[Stagename]);Project[Stagename] ="Bid Made"))

The error reads: "The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String."

[Bokazoit]

It says that the Count function will not work with string type. I used the function I put in here on my own data with a ProductId which is a number. Try to convert the stagename to an id, and I am pretty sure it will work 🙂

[Anonymous]

COUNT doesn't count string types of cells. Instead use COUNTA