Total not showing (DAX Measure)

[LAMLAM]

Hi,

I'm having trouble with a measure not showing any total in tables and other visuals. The idea with the measure is to have a running total for projects that are open given the selected period (month). I'm pretty sure that it is the date filtering happening on the project dimension in the measure that interferes with the total.

On the row-level the measure works just fine...

Measure= 

CALCULATE(

   SUM('Finance DW_FactFinance'[Value]);

      'Shared DW_DimVersion'[VersionName]="Actual";

      DATESBETWEEN(DimDate[Date];DATE(1900;1;1);MAX(DimDate[Date]));

      FILTER(DimProject;

         MAX(DimDate[Date])>=MAX(DimProject[StartDate]) &&

         OR(

            MAX(DimDate[Date])<=MAX(DimProject[ActualEndingDate]);

            MAX(DimProject[ActualEndingDate])=BLANK())))

I have tried to include the measure in a SUMX(SUMMARIZE(...),Measure) syntax, but either this is not possible for this case or I don't have the logic down.

Anyone with some ideas or advice?

Best regards

[Anonymous]

Try wrapping your function with SUMX:

SUMX ( 
VALUES( T [C] ), //whatever is on rows to sum up
CALCULATE(
   SUM('Finance DW_FactFinance'[Value]);
      'Shared DW_DimVersion'[VersionName]="Actual";
      DATESBETWEEN(DimDate[Date];DATE(1900;1;1);MAX(DimDate[Date]));
      FILTER(DimProject;
         MAX(DimDate[Date])>=MAX(DimProject[StartDate]) &&
         OR(
            MAX(DimDate[Date])<=MAX(DimProject[ActualEndingDate]);
            MAX(DimProject[ActualEndingDate])=BLANK())))
           )

[LAMLAM]

Hi @Anonymous ,

No luck, unfortunately.

I don't know if it helps to say that my data model looks like this. Basically, I wanna be able to filter on a specific organization, which then only returns a given set of projects, which then again filters my fact. At the same time I want to only have open projects, hence my last 5 lines in the measure.

[Anonymous]

any chance you can load some sample data?

[LAMLAM]

Hi again,

I realized a PBI file might be easier to work with 😉

Here's a link: WeTransfer - PBI Sample File

[v-lili6-msft · Accepted solution]

hi, @LAMLAM 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

And for your case, just add a measure like this:

Measure 3 = var _table=SUMMARIZE(DimProject,DimProject[ProjectName],DimProject[StartDate],DimProject[EndDate],"_value",[Measure]) return
SUMX(_table,[_value])

Result:

Best Regards,

Lin

[LAMLAM]

Hi @v-lili6-msft ,

Amazing, it works exactly as intended. Sorry about the sloppy information provided initially 🙂 

I've actually tried with almost the same measure as you provided, the only difference is I didn't include the "_value" and [Measure] in the summarize, but instead had the [Measure] as the expression in the SUMX. Futhermore, it was all done in one measure, not two separate ones. And having played around with your solution it seems like the main trick is having the base measure as a separate measure, and not just a variable, and then another measure to wrap it all up in a SUMX(SUMMARIZE(..)..).

Could you explain why the new measure works? What is the logic behind adding the "_value" in the summarize and then referring to it again in the SUMX? And why does it help to have to separate measures and not one big one with multiple variables? 

Whatever the case, thanks a lot! 🙂 

[jabrillo]

Hi, can you provide the formula you created? Thanks...

[LAMLAM]

Hi @Anonymous ,

Try this link with excel sample data: WeTransfer

So once again. What I am trying to achieve is to get the sum of my values, but only for projects that are open in the period selected in the dimdate dimension.

Best regards