Help with accumulated values

[vgarciasouza]

i have the following dax formula to count accumulated:

I also have a related table to "escritorio ajustado" and a related table to "Regional"

=CALCULATE(
COUNTROWS(franqueados);
franqueados[Papel na rede] =  "Franquia";
FILTER(ALLEXCEPT(franqueados; franqueados[Escritório Ajustado]; franqueados[Regional]);
franqueados[Data da ativação]<= MAX('Calendar'[Date])))

 the problem is that when I build the pivot table with the related tables on the rows, it doesnt filter for each "escritorio"and "regional". what am I missing here?

[NickDanger]

If you want to use the new Power BI Preview Feature of Visual Calculations, it may be very simple.
First make sure the feature is turned on in your settings:

Make a calculated column in your fact that is simply a value of "1" if the "Papel na rede" = "Franquia".
 

Add a "New Calculation"

Add the new column in your visual as a value

Choose the "Running Sum" calculation from the fx button and put the "Sum" of your column as the field"

Lastly, "Hide" the Sum of your measure in the fields pane

Let me know if this does the calculation as you expected!

Thanks
Nick

[vgarciasouza]

Hello @NickDanger , thank you for the effort!

I needed it as a measure, because I'll use it in calculations further down the road.  Also, I needed it to be accumulating int time, therefore the total in 2024 should be 884.

[v-jiewu-msft]

Hi @vgarciasouza ,

Please try the following methods and check if they can solve your problem:

1.Ensure that the relationships between the tables are correctly set up.

2.Verify that the relationships are active.

3.Adjust the DAX to the following formula.

= CALCULATE(
    COUNTROWS(franqueados);
    franqueados[Papel na rede] = "Franquia";
    FILTER(
        ALLEXCEPT(franqueados; franqueados[Escritório Ajustado]; franqueados[Regional]);
        franqueados[Data da ativação] <= MAX('Calendar'[Date]);
        franqueados[Escritório Ajustado] IN VALUES('Table'[Escritório Ajustado]);
        franqueados[Regional] IN VALUES('Table1'[Regional])
    )
)

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vgarciasouza · Accepted solution]

I got it. the issue was that I wal filtering on the "franqueados" table, while I should be filtering on the "calendar" table.. Here is the measure that generates the expected running total:

=CALCULATE(
   COUNTROWS(franqueados);
   franqueados[Papel na rede] =  "Franquia";
   FILTER(
      ALLSELECTED('Calendar'[Date]);
      ISONORAFTER('Calendar'[Date]; MAX('Calendar'[Date]); DESC
      )
   )
)

Thank you very much @v-jiewu-msft and @NickDanger for participating on this thread and helping figuring it out!

Best regards,

Victor Hugo.

[vgarciasouza]

Hello @v-jiewu-msft , I didn't managed to make this formula work. You can find the file with a sample of the data here.

The measure I am trying to figure out is cFranqueadosAcumulados. Thank you for your help,

best Regards,

Victort Hugo.