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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Need Help Fixing Measure - Summing a count

Hi All, 

 

So i have the following measures:

 

Current Month - Selects the Cost Center for the Currently Selected Month

 

Current Month = 
var FileDate = SELECTEDVALUE(Dates[File Name])
return
IF(ISBLANK((CALCULATE(SELECTEDVALUE(Workday[Cost Center - ID]), Workday[File Name] = FileDate))), "Exit", CALCULATE(SELECTEDVALUE(Workday[Cost Center - ID]), Workday[File Name] = FileDate ))

 

 

 


Previous Month Cost Center - Calculated Column that gets the prior months Cost Center for each row 

 

Previous Month Cost Center = 
VAR __prevDate = 
CALCULATE( 
    MAX( Workday[File Name] ), 
    FILTER( 
        ALLEXCEPT( 'Workday', Workday[Associate ID] ),  
        'Workday'[File Name] < MAX( Workday[File Name] )
    )
)
RETURN
    CALCULATE( MAX( Workday[Cost Center - ID]), ALLEXCEPT( 'Workday', Workday[Associate ID] ), Workday[File Name] = __prevDate )

 

 

Transfer Check - Checks if the Prior and Current Months Cost Centers have changed

 

 

TransferCheck = IF(AND([Current Month] <> [Prev], AND([Current Month] <> "", [Prev] <> "")), 1, 0)

 

 

ExitCheck - Checks to see if a record existed in previous month, but not the current month

 

ExitCheck = IF([Current Month] = "Exit", 1, 0)

 

 

 

I now want to summarize the ExitCheck flag. I have successfully done this with other measures, such as "TransferCheck" which is the following:

 

 

Transfer Count = 
sumx(Values(Workday[Associate ID]),[TransferCheck])

 

 

This gives me my desired outputs, showing a count by Business for "Transfers". However, when I apply this exact same logic to "ExitCheck", i only get zeroes. Is there something different i should be doing?

 

 

Also, here is my (simple) data model

 

Capture.PNG

Thanks!

 

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

Hi  @Anonymous ,

I created some data:

vyangliumsft_0-1628229468834.png

Here are the steps you can follow:

1. If you want to calculate the total of [ExitCheck], you can create measure:

all_sum = SUMX(ALL('Table'),'Table'[ExitCheck])

2. If you want to calculate [ExitCheck] in groups, you can create measure:

group_sum = SUMX(FILTER(ALL('Table'),[ExitCheck]=MAX('Table'[ExitCheck])),[ExitCheck])

3. Result:

vyangliumsft_1-1628229468835.png

If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Best Regards,

Liu Yang

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

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

I created some data:

vyangliumsft_0-1628229468834.png

Here are the steps you can follow:

1. If you want to calculate the total of [ExitCheck], you can create measure:

all_sum = SUMX(ALL('Table'),'Table'[ExitCheck])

2. If you want to calculate [ExitCheck] in groups, you can create measure:

group_sum = SUMX(FILTER(ALL('Table'),[ExitCheck]=MAX('Table'[ExitCheck])),[ExitCheck])

3. Result:

vyangliumsft_1-1628229468835.png

If the result does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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