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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors