MTD based on condition

[raassd]

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 !

[v-yalanwu-msft]

Hi, @raassd ;

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.

[v-yalanwu-msft · Accepted solution]

Hi, @raassd ,

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.

[amitchandak]

@raassd , 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]))

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[raassd]

@amitchandak ,

My requirment is to count number of yes divided by number of rows for each account under each vertical.

Thanks !

[amitchandak]

@raassd , 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]) )

[raassd]

@amitchandak ,

This measure is not working.

Giving an error.

Thanks !

[amitchandak]

@raassd , the measure is still not complete. can share formula with me

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[raassd]

@amitchandak ,

Here is the formula

Thanks !