Forum Discussion
MTD based on condition
Hi Team,
I have to calculate MTD as I have a table which has 4 columns as below, I have caluclate MTD based on the date.
Requirment = Number of yes under each account and divded by nuumber of rows from each account.
Example: If I have 75 rows for Acount A and 50 Yes, then 50/70*100 = 71.4%
this is my requirement. I will be using Vertical as slicer so values should change accoudingly.
Thanks !
Hi, Anonymous ,
You could create a measure as follow:
percent = DIVIDE ( CALCULATE ( COUNT ( [Account] ), FILTER ( ALLSELECTED ( 'Table' ), [Account] = MAX ( 'Table'[Account] ) && [Task Submision Status] = "Yes" ) ), CALCULATE ( COUNT ( [Account] ), FILTER ( ALLSELECTED ( 'Table' ), [Account] = MAX ( 'Table'[Account] ) ) ) )The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Anonymous , You can join all 4 of them with one date table. there will be inactive joins which can be activated using userelationship
example
calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),DATESMTD('Date'[Date]))
My requirment is to count number of yes divided by number of rows for each account under each vertical.
Thanks !
Anonymous , Unless you want Avg of Avg or Sum of Avg
This type of measure should help
Divide(countx(Filter(Table, Table[Task Submission Status]= "Yes") ,[account]), Count(Table[account]) )
Hi, Anonymous ,
You could create a measure as follow:
percent = DIVIDE ( CALCULATE ( COUNT ( [Account] ), FILTER ( ALLSELECTED ( 'Table' ), [Account] = MAX ( 'Table'[Account] ) && [Task Submision Status] = "Yes" ) ), CALCULATE ( COUNT ( [Account] ), FILTER ( ALLSELECTED ( 'Table' ), [Account] = MAX ( 'Table'[Account] ) ) ) )The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi, Anonymous ;
Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
