Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raassd
Helper I
Helper I

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.

raassd_0-1657700232801.png

 

Thanks !

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1658115903231.png


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.

View solution in original post

8 REPLIES 8
v-yalanwu-msft
Community Support
Community Support

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
Community Support
Community Support

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:

vyalanwumsft_0-1658115903231.png


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
Super User
Super User

@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...

@amitchandak ,

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

 

Thanks !

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

@amitchandak ,

This measure is not working.

Giving an error.

 

raassd_0-1657703806766.png

 

Thanks !

 

 

 

@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.

@amitchandak ,

Here is the formula

raassd_1-1657707384821.png

 

Thanks !

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.