Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure Total is wrong

masure total is wrong-     1) we have two masure in table visualization with total like masure A & B 2) requirement is if (A-B<0,0,A-B), so i need to takeout the all +ve value from A-B(and assign ...
Need Help
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Ok, I just tried these out:

    With both dimensions:

    Sum +ves =
SUMX (
    'FactTable',
    CALCULATE (
        IF ( [Sum Amount] - [Sum Target] < 0, 0, [Sum Amount] - [Sum Target] )
    )
)

     

     

    With 1 dimension

    Sum +ves Cat =
VAR _cat =
    ADDCOLUMNS (
        VALUES ( FactTable[Category] ),
        "_+ves",
            IF ( [Sum Amount] - [Sum Target] < 0, 0, [Sum Amount] - [Sum Target] )
    )
RETURN
    SUMX ( _cat, [_+ves] )

     

    I've attached the sample PBIX file

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    it works for me after doing some R&D.

    measure C = meaasure A- Measure B.

    +ve total = Calculate([C],FILTER(SUMMARIZE(FACTTABLE,PARENTDIMTABLE[COLUMN],FACTTABLE[COLUMN]),[C]>0))

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi,

 

I have a similar issue that isn't resolved. I tried using the HASNOFILTER with SUMX but I am still getting wrong total for the measure. I have more details on the problem below, hopefully I am able to communicate clearly as I am new to using Power BI

 

I am working with a timesheet table that shows how many hours employees are using for various activities daily. This timesheet also reports PTO and Holiday Hours. I am trying to compute the total available hours on a periodic basis- daily, weekly, monthly, etc. Using a weekly time period, the total available hours is calculated as: 40 hours (i.e. 5 days * 8 hours) - PTO hours - Holiday hours. 

 

In the timesheet table, I have created a measure that sums all hours logged as PTO (PTO is conditioned on the activity field i.e. activity would be vacation, sick, etc). The query is 

PTOMeasure = sum(UserReport_20220630021724[IsPTO_Hours])

 

I have created a Calendar table that has fields:

Date Column: All dates in the year 2022 (increments daily, the current max date is today's date),

'Work hours' (Values is 8 hours for dates on Mon-Fri, 0 for Sat and Sun)

Holiday Hours (Value is 8 hours for every holiday in the year; Holidays are fixed days annually). 

To calculate the Available Hours, I created a measure 

AvailableHours = calculate((sum('Calendar'[WorkHours]) - sum('Calendar'[Holiday.Hours]) - UserReport_20220630021724[PTOMeasure]), 'Calendar'[DayType] = "Weekday")
 
The available hours computes correctly based on the above query but the total is wrong, same as the measure using HASONEFILTER 
CorrectAvailabilityHours = if(HASONEFILTER('Calendar'[Year]),[AvailableHours],(sumx(values('Calendar'[Year]), 'Calendar'[AvailableHours])))
 
I would appreciate help from the community in resolving this challenge.