cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
lingleji
Frequent Visitor

Issues with Calculation Groups - Cumulative Total and Formatting

Hi super users.  I'm having a couple of issues putting the finishing touches on something with calculation groups.  I've run into 2 issues.

1. I've got a cumulative total calculation that works as a measure, but when I try to use it (even modified), it isn't returning the correct values.  

This measure is working.

 

Cumulative Revenue =

VAR MonthStart = DISTINCT (

SELECTCOLUMNS (

FILTER('Fiscal Calendar', 'Fiscal Calendar' [Calendar_Date] = Today()-1) ,

"Fiscal Calendar", [Month_Start_Date]

))

 

This is what I've tried using in the Calculation Group for Cumulative Revenue, but it just returns the total for the current day.

RETURN CALCULATE ([Total Revenue],

 FILTER(ALLSELECTED('Collected Payments'),

'Collected Payments'[Invoice Closed Date] <= MAX('Collected Payments'[Invoice Closed Date])

 

CALCULATE (CALCULATE(SUM('Collected Payments'[Amount]), FILTER('Collected Payments', 'Collected Payments'[Category] <> "Tips" && ('Collected Payments'[Payment Type] = "Card" || 'Collected Payments'[Payment Type] = "Cash" || 'Collected Payments'[Payment Type] = "Check" || 'Collected Payments'[Payment Type] = "Custom"))),

 FILTER(ALLSELECTED('Fiscal Calendar'),

 'Fiscal Calendar'[Calendar_Date] <= MAX( 'Fiscal Calendar'[Calendar_Date])

 

 

Cumulative Actuals - Working.PNG

 

Cumulative Actuals.PNG

 

 

2.  I've got a variance calculation in the calculation group for Var vs PY %.  Here is the DAX that I'm using.  When I don't include the FORMAT as % , it correctly is hidden (I don't have prior year data).  However, when I add it to format the values that do return a value, it returns 0% for the Last Year (which has no prior year actuals to compare to so should not be returned).  I saw another couple of posts that mention changing the Data Type and Format, but it is not allowing me to convert from Text.

Formatting.PNG

 

 

With formatting

VAR Act = SELECTEDMEASURE()Var Targ = CALCULATE(SELECTEDMEASURE(), 'CG_Select total'[Select Total]="Target")VAR CurrentMeasure =    SELECTEDMEASURE ()VAR PriorMeasure =    CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Years] = "Last Year" )RETURN    IF (        CurrentMeasure = BLANK ()            || Targ = BLANK (),        BLANK (),        FORMAT(((CurrentMeasure - Targ) / Targ), "0%")    )

 

 

Var vs PY with Formatting.PNG

 

Var vs PY without Formatting.PNG

 

Any assistance is greatly appreciated.  Thanks in advance!

1 REPLY 1
Anonymous
Not applicable

Hi @lingleji ,

 

ShundaSteph_1-1660026299027.png

May I ask what value does this formula correspond to in your screenshot?

 

And you may try to use DIVDE() function for division. It returns alternate result or BLANK() on division by 0.

DIVIDE function (DAX) - DAX | Microsoft Docs

 

Best Regards,

ShundaSteph

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors