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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
S3
Helper III
Helper III

SUMX ADDCOLUMN SUMMARIZE Measure when using a card visual

Hello everyone, 

I'm having a very difficult time understanding this problem:

first, I created a measure to calculate the Revenue of previous month and wanted to put this in a Card Visual. It returned BLANK. Here's the measure I used for it:

Test Revenue = CALCULATE(
SUM('Table'[Revenue]), PREVIOUSMONTH('Calendar'[Date])
)

When I put it in a table with a date column, it worked, but no total (hence the BLANK in Card Visual)

After searching the internet, I found this site explaining perfectly:

https://forum.enterprisedna.co/t/mtd-value-is-showing-blank-in-card/19858/2

So now my Test Revenue measure is:

test Revenue = 

SUMX (ADDCOLUMNS(
SUMMARIZE (
'Daily Countries','Daily Countries'[revenue]),
"@actualrevenue", [revenue]
),
[@revenue]
)


When I used it, I got BLANK as well. Then I removed the PREVIOUSMMONTH filter from the calculation, and it returned a value. However, this is what it returned (in the table below). The test Revenue is the measure and the revenue is simply the column from the original table.
8F3EBB53-4D80-48D6-992B-371525F9664D.jpeg
And I'm just lost why would the test Revenue show 24,22 + only one of 5,09 and not the 3 of them. Becaus eof the Summarize? 

I'm really lost at his point, so I'd be happy about any explanation. Thank you in advance.

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @S3 
Because you are summarizing by the same column that you are summing. This summary table will contain only 3 rows: 24.22 ; 5.09 ; 0.00 and those are the numbers that need to be summed. 
you can use

test Revenue =
SUMX (
    VALUES ( 'Calendar'[Month] ),
    CALCULATE (
        SUM ( 'Daily Countries'[actual_revenue] ),
        PREVIOUSMONTH ( 'Calendar'[Date] )
    )
)

View solution in original post

@S3 

You may try

test Revenue =
SUMX (
    LASTNONBLANK ( 'Dates'[Monthid], SUM ( 'Daily Countries'[revenue] ) ),
    CALCULATE (
        SUM ( 'Daily Countries'[revenue] ),
        PREVIOUSMONTH ( 'Dates'[Date] )
    )
)

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @S3 
Because you are summarizing by the same column that you are summing. This summary table will contain only 3 rows: 24.22 ; 5.09 ; 0.00 and those are the numbers that need to be summed. 
you can use

test Revenue =
SUMX (
    VALUES ( 'Calendar'[Month] ),
    CALCULATE (
        SUM ( 'Daily Countries'[actual_revenue] ),
        PREVIOUSMONTH ( 'Calendar'[Date] )
    )
)

Hello again 🙂 

I have a question regarding this measure please: when I put it in the table, it shows the previous month perfectly, but I now would like to put it in a card and it gives me the total of course. 

I tried another previous month measure, which works on the card visual showing previous month, but doesn't change when I filter out months. It also doesn't show row by row when put in a table.

The image below explains everyting. Ideally, I would like your measure (test revenue) to act like my measure of Revenue This Month: shoing row by row and at the end showing only for this month (in the test revenue case, only for past month)

Here is the measure I have for Revenue This Month:

Revenue This Month =
VAR LastDateWithData =
LASTNONBLANK ( 'Dates'[Monthid], SUM('Daily Countries'[revenue] ))

Return
CALCULATE(
SUMX (
SUMMARIZE (
'Dates',
'Dates'[Monthid],
"@Currentmonthrevenue", SUM('Daily Countries'[revenue])
),
[@Currentmonthrevenue]
),LastDateWithData )

783B2045-EDBF-41EF-BC6C-38C4BEA0C460_4_5005_c.jpeg

I really appreciate the help!

@S3 

You may try

test Revenue =
SUMX (
    LASTNONBLANK ( 'Dates'[Monthid], SUM ( 'Daily Countries'[revenue] ) ),
    CALCULATE (
        SUM ( 'Daily Countries'[revenue] ),
        PREVIOUSMONTH ( 'Dates'[Date] )
    )
)

It works, thank you so much!

thank youu

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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