topic Re: Divide all rows by a Single NON PARENT Row in DAX Commands and Tips

Divide all rows by a Single NON PARENT Row

JonathanK1999 — Mon, 13 Mar 2023 19:46:17 GMT

I want to calculate in the gray column, each category divided by the ventas category. (ex. divide consumo/ventas, bebidas/ventas, comestibles/ventas)

For example: divide bebidas row by the total ventas row to show the percentage of how much the bebidas row reprents of total ventas. (if bebidas is 10 and total ventas 100, it should give me 10%). IMPORTANT: VENTAS IS NOT THE BEBIDAS PARENT ROW

I want to just have a formula that applies for every row, as I know that I can get a total individual formula for each row by dividing by each row individually (see example below). But the example below just gives me what percentage of ventas is represented by bebidas in the example below, not for every row

% de ventas en bebidas = (DIVIDE((CALCULATE(SUM(ConsolidadoPG[Valor]), ConsolidadoPG[Sub Categoria]="Bebidas")), CALCULATE(SUM(ConsolidadoPG[Valor]), ConsolidadoPG[Categoria]="Ventas")))

Re: Divide all rows by a Single NON PARENT Row

andhiii079845 — Mon, 13 Mar 2023 19:55:27 GMT

Try this: You can acess the values of the categories via "SELECTEDVALUE"

% de ventas en bebidas =
VAR _selectcat = SELECTEDVALUE(ConsolidadoPG[Categoria])

RETURN
(DIVIDE((CALCULATE(SUM(ConsolidadoPG[Valor]), ConsolidadoPG[Sub Categoria]="Bebidas")), CALCULATE(SUM(ConsolidadoPG[Valor]), ConsolidadoPG[Categoria]=_selectcat)))

Re: Divide all rows by a Single NON PARENT Row

JonathanK1999 — Mon, 13 Mar 2023 20:00:17 GMT

that would just give me for bebidas, I need to for all of the rows

Re: Divide all rows by a Single NON PARENT Row

andhiii079845 — Mon, 13 Mar 2023 20:03:31 GMT

Try this, but without a better example i do not understand your calc:

% de ventas en bebidas =
VAR _selectcat = SELECTEDVALUE(ConsolidadoPG[Categoria])
VAR _selectsub = SELECTEDVALUE(ConsolidadoPG[Sub Categoria])

RETURN
(DIVIDE((CALCULATE(SUM(ConsolidadoPG[Valor]), ConsolidadoPG[Sub Categoria]=__selectsub)), CALCULATE(SUM(ConsolidadoPG[Valor]), ConsolidadoPG[Categoria]=_selectcat)))

Re: Divide all rows by a Single NON PARENT Row

tamerj1 — Mon, 13 Mar 2023 20:05:19 GMT

Hi JonathanK1999

please try

% de ventas en bebidas =
(
DIVIDE (
SUM ( ConsolidadoPG[Valor] ),
CALCULATE (
SUM ( ConsolidadoPG[Valor] ),
ConsolidadoPG[Categoria] = "Ventas",
ALL ( ConsolidadoPG[Sub Categoria] )
)
)
)

Re: Divide all rows by a Single NON PARENT Row

JonathanK1999 — Mon, 13 Mar 2023 22:13:04 GMT

Won't work either. Think about it like an income statement, were ventas is sales, and I want to see how much each cost represents of sales. Doing this way I am just filtering for data that contains sales

Re: Divide all rows by a Single NON PARENT Row

JonathanK1999 — Mon, 13 Mar 2023 22:13:37 GMT

Won't work 😔. Think about it like an income statement, were ventas is sales, and I want to see how much each cost represents of sales. Doing this way I am just filtering for data that contains sales

Re: Divide all rows by a Single NON PARENT Row

tamerj1 — Tue, 14 Mar 2023 04:24:20 GMT

JonathanK1999
It should work. What results are you getting. However, you may also try

% de ventas en bebidas = ( DIVIDE ( SUM ( ConsolidadoPG[Valor] ), CALCULATE ( SUM ( ConsolidadoPG[Valor] ), ConsolidadoPG[Categoria] = "Ventas", ALL ( ConsolidadoPG ) ) ) )

Re: Divide all rows by a Single NON PARENT Row

JonathanK1999 — Tue, 14 Mar 2023 13:15:28 GMT

Same problem happens, which is I do not get data for sub categories that don't represent the ventas row. In other words, I need to divide completely separate data points. Look at the table I created with an example of the values I need. % of sales is each row divided by the sales value. Sales and Costs are parent rows. Screen shot at the bottom is results I am getting.

	Value	% of Sales
Sales	$160.00
Water	$100.00	62.50%
Sushi	$10.00	6.25%
Pasta	$20.00	12.50%
Delivery	$30.00	18.75%
Costs	$76.00	47.50%
Bebidas	$50.00	31.25%
Comestibles	$3.00	1.88%
Daportare	$4.00	2.50%
Empaques	$2.00	1.25%
Evenetos	$7.00	4.38%
Paletas	$10.00	6.25%
Net	$84.00

Re: Divide all rows by a Single NON PARENT Row

tamerj1 — Tue, 14 Mar 2023 13:21:01 GMT

JonathanK1999

I think there is a value for subcategories. But it is very small and hence rounded to zero. My guess is that you have outside slicers from the same table. Clear all filters and see what you get. If this is the case then you need to place all of these columns inside the ALLEXCEPT as follows

% de ventas en bebidas = DIVIDE ( SUM ( ConsolidadoPG[Valor] ), CALCULATE ( SUM ( ConsolidadoPG[Valor] ), ConsolidadoPG[Categoria] = "Ventas", ALLEXCEPT ( ConsolidadoPG, ConsolidadoPG[Column1], ConsolidadoPG[Column2], etc.. ) ) )

Re: Divide all rows by a Single NON PARENT Row

JonathanK1999 — Tue, 14 Mar 2023 14:04:44 GMT

Doesn't work, I guess I'll have to create all the individual formulas. Is there a way to add different formulas all in the same column in a table?