Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 ,................
Solved! Go to Solution.
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
Proud to be a Super User!
Paul on Linkedin.
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))
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!
Proud to be a Super User!
Paul on Linkedin.
Sure. Thanks for the response
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
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
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))
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] )
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:
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
Proud to be a Super User!
Paul on Linkedin.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |