cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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.

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

Any assistance is greatly appreciated.  Thanks in advance!

Anonymous
Not applicable

Hi @lingleji ,

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors