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
Anonymous
Not applicable

Running total of a measure based on an IF statement?

Hi! I have the following table

wherein the first column M1Count counts all the flags of all the StoreID, the 2nd column M2Count is the same measure as M1Count but for the next month. M3Count is the same but for the next 2nd month.

 

1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

M2 and M3 count relies on an IF statement such as this:

 

CountBlank2 = CALCULATE(COUNTX ( VALUES ( 'Store Invoice'[Store ID] ), 'Secondary Sales Invoices'[Net Secondary Amount - Invoiced]) ,NEXTMONTH('Date'[Date]))
 
M2Count = IF([CountBlank2] = BLANK(), [M1Count], 1)
 
My question is how do I get the running total of M2Count and M3Count because I can't do it with SUM or CALCULATE functions as it does not accept my column.

UPDATE: I have changed the formula of the M2Count and M3Count to the following:

M2Count = CALCULATE(IF([CountBlank2] = BLANK(), [M1Count], [CountBlank2]))
M3Count = (IF([CountBlank4] = BLANK(), [M2Count], [CountBlank4]))
 
Although it does sum the column, the resulting total is wrong
4.PNG
 
Upon validation in Excel, the Total should be the following:
5.PNG
1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Power-BI-desktop-not-calculating-total-for-measure-in-Matrix/m-p/290724#M128037

Community Support Team _ Sam Zha
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

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Power-BI-desktop-not-calculating-total-for-measure-in-Matrix/m-p/290724#M128037

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

UPDATE: I have changed the formula of the M2Count and M3Count to the following:

M2Count = CALCULATE(IF([CountBlank2] = BLANK(), [M1Count], [CountBlank2]))
M3Count = (IF([CountBlank4] = BLANK(), [M2Count], [CountBlank4]))
 
Although it does sum the column, the resulting total is wrong
4.PNG
 
Upon validation in Excel, the Total should be the following:
5.PNG
Anonymous
Not applicable

Can anyone help me with this? 😞 Thanks a lot.

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.