cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
coreyweiss Member
Member

Total of Measure Incorrect

Hello,

 

I have this table,

 

Capture222.PNG

 

 but the total is wrong (its missing the 120 +120 to equal 3205 for Tax Place holder and 2592 for Cash Place holder)

 

My measures are:

 

Tax Place Holder = IF(ISBLANK(SUM('Pi Data'[Taxes])),5*24,SUM('Pi Data'[Taxes]))

 

and 

 

Cash Place Holder = 18*24.
 
Note: [Cash Place Holder] is just for showing what the problem is. It seems that it should be like SUM(18*24)
 
My objective is to have [Tax Place Holder] have the correct total. Whenever there is not a value for that day, it is to insert 5*24.
 
 
Here is my data:
 
 
DateTaxes
10/2/2016 0:00635.802
10/3/2016 0:00649.527
10/6/2016 0:00827.314
10/4/2016 0:00852.5352
 
 
Note that 9/30 and 10/1 do not exist in my table with taxes, but the place holder would have values for these dates from a calendar table.
 
Any thoughts?
 
Thanks!
 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Total of Measure Incorrect

Hi @coreyweiss

create measures

blank_flag = IF(SUM('Pi Data'[Taxes])=BLANK(),1,0)

Tax Place Holder2 =
VAR count_blank =
    COUNTX ( FILTER ( ALLSELECTED ( 'date' ), [blank_flag] = 1 ), [blank_flag] )
VAR tax1 =
    IF ( ISBLANK ( SUM ( 'Pi Data'[Taxes] ) ), 5 * 24, SUM ( 'Pi Data'[Taxes] ) )
RETURN
    IF (
        HASONEVALUE ( 'date'[Date] ),
        tax1,
        CALCULATE ( SUM ( 'Pi Data'[Taxes] ), ALLSELECTED ( 'date'[Date] ) )
            + 120 * count_blank
    )

Cash Place Holder2 = var sum1=18*24 return IF(HASONEVALUE('date'[Date]),sum1,SUMX(ALLSELECTED('date'),sum1))

2.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Total of Measure Incorrect

Hi @coreyweiss

create measures

blank_flag = IF(SUM('Pi Data'[Taxes])=BLANK(),1,0)

Tax Place Holder2 =
VAR count_blank =
    COUNTX ( FILTER ( ALLSELECTED ( 'date' ), [blank_flag] = 1 ), [blank_flag] )
VAR tax1 =
    IF ( ISBLANK ( SUM ( 'Pi Data'[Taxes] ) ), 5 * 24, SUM ( 'Pi Data'[Taxes] ) )
RETURN
    IF (
        HASONEVALUE ( 'date'[Date] ),
        tax1,
        CALCULATE ( SUM ( 'Pi Data'[Taxes] ), ALLSELECTED ( 'date'[Date] ) )
            + 120 * count_blank
    )

Cash Place Holder2 = var sum1=18*24 return IF(HASONEVALUE('date'[Date]),sum1,SUMX(ALLSELECTED('date'),sum1))

2.png

 

Best Regards

Maggie

View solution in original post

coreyweiss Member
Member

Re: Total of Measure Incorrect

Thanks! it works. Crazy that it needs to be that complicated haha!!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 219 members 2,114 guests
Please welcome our newest community members: