Forum Discussion
What am i doing wrong
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)
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.
14 Replies
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"))
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."
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
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
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")
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
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
