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
vgarciasouza
Frequent Visitor

Help with accumulated values

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?

1 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.

View solution in original post

5 REPLIES 5
NickDanger
Regular Visitor

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:

NickDanger_6-1708546248515.png

 



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

NickDanger_0-1708547293824.png

 

 

 

Add a "New Calculation"

NickDanger_2-1708545961529.png

 

Add the new column in your visual as a value

NickDanger_3-1708545984872.png

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

NickDanger_4-1708546130806.png

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

NickDanger_5-1708546180719.png


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

Thanks
Nick





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
Community Support
Community Support

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.

 

 

 

 

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.

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors