cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

cannot aggregate measure

Hi there,

 

I ve got a problem with measure dax. I cannot sum in group from measure which I ve created.

 

First I create a calculated column

 

totalday= (DATEDIFF(UW[Hieuluc Tu],UW[Hieuluc Den],DAY)

 

then I created a measure as below

earned_day = (CALCULATE(COUNTROWS('Date'),FILTER('Date','Date'[Date] >=MIN(UW[Hieuluc Tu]) && 'Date'[Date] <= MIN(UW[Hieuluc Den]))))/MIN(UW[totalday])
 
It's righ for each row calculation but when I do a sum for the year it's not correct anymore.
 
Do you have any idea?
 
Thanks for all!
a1.PNG

 

 
4 REPLIES 4
Highlighted
Super User II
Super User II

Re: cannot aggregate measure

See if this works:

earned_day =
SUMX (
    VALUES ( 'Date'[Year] ),
    DIVIDE (
        CALCULATE (
            COUNTROWS ( 'Date' ),
            FILTER (
                ALL ( 'Date' ),
                'Date'[Date] >= MIN ( UW[Hieuluc Tu] )
                    && 'Date'[Date] <= MIN ( UW[Hieuluc Den] )
            )
        ),
        VAR MinTotalDay =
            MIN ( UW[totalday] )
        RETURN
            IF ( MinTotalDay = 0, 1, MinTotalDay )
    )
)

The reason for infinity error is because at the denominator there is 0 for 2020  so you will have to check for the 0.

 


Thank you,
Antriksh Sharma
Highlighted
Regular Visitor

Re: cannot aggregate measure

Thanks for your reply

 

Your dax can fix the infinity problem but it still doesnt correct the total of row.  I need to calculate the total by year. I cannot use sum function for a measure. The first measure just calculate for each row and I dont know how I can aggregrate for a group.

 

a2.PNG

Highlighted
Super User II
Super User II

Re: cannot aggregate measure

Can you please upload the file to google drive/one drive/dropbox and share the link and mark what values you want at the each row in the visual.

Thank you,
Antriksh Sharma
Highlighted
Solution Sage
Solution Sage

Re: cannot aggregate measure

You have to know what (and how) you want to calculate when you have many dates in the current context. If you don't know how to aggregate on the conceptual level (theory), you can't know the formula either (practice).

You have to give us the rule that produces the correct number if you have many days visible in the current context. I believe the calculation is correct on the day level or for the individual row in your UW table... You have not stated the problem in full, hence doubts.

Best
D

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors