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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
debasnhu
Frequent Visitor

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 -ve and 0 as 0), Then the total should be there,I am able to achive The above but Total is wrong (when i am adding all values indivually total is different which is actual )

using following masure----

My masure C= A-B, Total = SUMX(FILTER(ALLSELECTED(Table[column]),[C]>0),[C]) note-: Working wrong at Total level

here Table[column] is ref. coloum for row filter... Thanks 

My masure 2- TOTAL =
VAR Z =
ADDCOLUMNS (
SUMMARIZE (
table[column]
),
"C",
IF(A - B<0,0,
A-B)
)
RETURN
{SUMX(Z,[C])}

 

MASURE 3- C= A-B

 

THEN

 

CS= SUMX(SUMMARIZE(TABLE[COLUMN]
"Z",IF([C]<0,0,[C])),
[C])

Note- All masure are equating for each row but as masure is not working on concept of totaling based on row value getting wrong total is there any way to focus in each row and calculate total correctly as i don,t have option to create calculate column and total is importent to be in report...

 

 

Thanks ,................

2 ACCEPTED SOLUTIONS

Ok, I just tried these out:

facttable.png

With both dimensions:

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

2 dim.png

 

 

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] )

1 dim.png

 

I've attached the sample PBIX file

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

debasnhu
Frequent Visitor

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))

 

View solution in original post

7 REPLIES 7
PaulDBrown
Community Champion
Community Champion

@ifeayeni 

It is probably best if you started a new thread with your question. Also please provide sample non confidential data or PBIX file and a depiction of the expected outcome. Thanks!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Sure. Thanks for the response

ifeayeni
Frequent Visitor

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.
debasnhu
Frequent Visitor

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))

 

PaulDBrown
Community Champion
Community Champion

Try:

With Totals =
VAR Z =
    ADDCOLUMNS (
        VALUES ( table[column] ),
        "C",
            CALCULATE (
                IF ( [Measure A ] - [Measure B] < 0, 0, [Measure A ] - [Measure B] )
            )
    )
RETURN
    SUMX ( Z, [C] )




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Appriciate your response!!

its giving same result as measure 2 mantioned above . still not getting the correct total should we use 

HASONEFILTER by increasing no of argument with nested if.. please share thoughts

Ok, I just tried these out:

facttable.png

With both dimensions:

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

2 dim.png

 

 

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] )

1 dim.png

 

I've attached the sample PBIX file

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors