topic Re: Solve problems with particular totals in DAX Commands and Tips

Solve problems with particular totals

sportdata — Fri, 12 Aug 2022 14:38:11 GMT

I'm having trouble with the total for a particular column.
Try to put together a measure, which works differently depending on the level of granularity. And I'm happy with the results, but when it gets to the total, I need the results it shows to be different than what it's showing now.
In the image are all the details, but I will try to explain it too.
Specifically, I need the "total" column "mts / min", to show the division of the total column "Distances (m)" / the total column "Duration (min).

Add the formulas that I use in the picture and send the pbix file

I'm not sure you can actually do that.

Thank you

https://drive.google.com/file/d/1D7KQmIcsvkCn8lDJnbm2rkIROCZQmtf1/view?usp=sharing

Re: Solve problems with particular totals

Anonymous — Fri, 12 Aug 2022 15:18:03 GMT

https://community.powerbi.com/t5/Power-Query/Row-Total-Average/td-p/965757
This might help you
You need a conditional statement to show diffrent values for totals

Re: Solve problems with particular totals

sportdata — Fri, 12 Aug 2022 20:15:47 GMT

OK, I'm new to all this, I'll try to use your suggestion.
From already thank you very much.

Diego

Re: Solve problems with particular totals

sportdata — Sat, 13 Aug 2022 13:06:50 GMT

I wanted to thank you. I implemented ISINSCOPE, and it worked.

The values are correct, but now if some athletes did exercise A, it shows me the data of exercise A, but it also shows me values in those players who did not do those exercises.

I hope you understand what I mean.

Thanks.

Hierrarchi m_min =

VAR dr_ath_ses = CALCULATE([m_min_calc],

ALLEXCEPT(Tabla2,Tabla2[Drill],Tabla2[Athlete],Tabla2[Session]))

VAR Ath_ses = CALCULATE([m_min_calc],

ALLEXCEPT(Tabla2,Tabla2[Athlete],Tabla2[Session]))

VAR ses = CALCULATE([m_min_calc],

ALLEXCEPT(Tabla2,Tabla2[Session]))

VAR grandtotal = Calculate(DIVIDE([Distance (m)],[Duration (min)]))

Return

SWITCH(

true(),

ISINSCOPE(Tabla2[Drill]), dr_ath_ses,

ISINSCOPE(Tabla2[Athlete]),Ath_ses,

ISINSCOPE(Tabla2[Athlete]),ses,grandtotal)

Re: Solve problems with particular totals

sportdata — Sat, 13 Aug 2022 16:17:16 GMT

I solve

thanks

Hierrarchi m_min =

VAR dr_ath_ses = CALCULATE([m_min_calc],

KEEPFILTERS(VALUES('Tabla2'[Athlete])),

ALLEXCEPT(Tabla2,Tabla2[Drill],Tabla2[Athlete],Tabla2[Session]))

VAR Ath_ses = CALCULATE([m_min_calc],

KEEPFILTERS(VALUES('Tabla2'[Athlete])),

ALLEXCEPT(Tabla2,Tabla2[Athlete],Tabla2[Session]))

VAR ses = CALCULATE([m_min_calc],

KEEPFILTERS(VALUES('Tabla2'[Athlete])),

ALLEXCEPT(Tabla2,Tabla2[Session]))

VAR grandtotal = Calculate(DIVIDE([Distance (m)],[Duration (min)]))

Return

SWITCH(

true(),

ISINSCOPE(Tabla2[Drill]), dr_ath_ses,

ISINSCOPE(Tabla2[Athlete]),Ath_ses,

ISINSCOPE(Tabla2[Athlete]),ses,grandtotal)