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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Osama14
Frequent Visitor

Getting the total value of a created Measure

I am still developing in power Bi, I am having an issue in getting the total value of a calculated measure, the measure is calculating daily consumption readings and i want to get the total for the selected period. 

This is the measure formula: 

Meter 1 Consumption =
VAR ConsumptionToday =
CALCULATE(SUM(ConsumptionDaily[QC Meter 1]))
VAR ConsumptionPriorDay =
CALCULATE(SUM(ConsumptionDaily[QC Meter 1]), PREVIOUSDAY('ConsumptionDaily'[Date]))
VAR DIFF =
ConsumptionToday - ConsumptionPriorDay
RETURN
IF(ISBLANK(ConsumptionPriorDay),BLANK(),IF(DIFF<0,DIFF+9999999,DIFF))

How to get the sum of Meter 1 Consumption, i have tried to use sumx and seen some solutions using HAS1Filter 

Sum of Meter 1 = CALCULATE(SUMX(ConsumptionDaily,[QC Meter 1 Consumption]))

But it is showing blank, 

If you can help please 

 

1 REPLY 1
Schmidtmayer
Helper I
Helper I

You should use the effect of a telescopic sum here.
Instead of summing over all of the daily consumptions, just calculate the difference between first consumption and last consumption within the time, using your logic:

Meter 1 Consumption =
VAR MaxDate = MAXX(Datecolumn)
VAR MinDate = MINX(Datecolumn)
VAR ConsumptionEnd =
CALCULATE(SUM(ConsumptionDaily[QC Meter 1]), date = MaxDate)
VAR ConsumptionStart =
CALCULATE(SUM(ConsumptionDaily[QC Meter 1]), Datecolumn = MinDate)
VAR DIFF =
ConsumptionEnd - ConsumptionStart
RETURN
IF(ISBLANK(ConsumptionStart),BLANK(),IF(DIFF<0,DIFF+9999999,DIFF))

Might need to substract one day from MinDate.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors