Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wmoura12
Regular Visitor

How can I divide a column by a fixed value

How can I divide a column by a fixed value

 

I´m in trouble with an issue that seems to be easy to solve, but I can´t do it. I have a Matrix and i need to create a measure or a column dividing MOV YTD of the row by MOV YTD OF RECEITA LIQUIDA to calculate the percentage. For Example:

 

Capturar.JPGfigura.JPG

 

 

 

 

MOV YTD = CALCULATE(sum(dRazao[VALOR]); DATESYTD(dCalendario[Data]))

 

% Vert_YTD =


VAR Vert_MOV_YTD_01 = CALCULATE([RL YTD]; ALL('dEBITDA'[CONTA_EBITDA]; 'dEBITDA'[#EBITDA]))

VAR Vert_MOV_YTD_02 = CALCULATE([MOV YTD];ALL('dRazao'[CLASSECONTA2]);ALL('dRazao'[ORDEMDRE]))

RETURN DIVIDE([MOV YTD];IF(DIVIDE ([MOV YTD] ; Vert_MOV_YTD_02) = 1 ; if(ISBLANK(Vert_MOV_YTD_01); Vert_MOV_YTD_02;Vert_MOV_YTD_01) ;Vert_MOV_YTD_02))

 

 

WALLACE

 

1 ACCEPTED SOLUTION

Maybe you can change the table

Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA");dRazao)

 

The number has changed but it is wrong.

 

After several attempts, I succeeded

 

%Vert RLiq =

VAR DEN_01 = CALCULATE([RL YTD];ALLSELECTED(dRazao); FILTERS(dColigada[Empresa_Simples]))
VAR NUM_01 = [MOV YTD]

RETURN
DIVIDE(NUM_01;DEN_01;0)

 

ok.JPG

View solution in original post

8 REPLIES 8
Floriankx
Solution Sage
Solution Sage

Hello,

 

you can use something like:

 

Calculate([MOV YTD];ALL(Table):Filter(Table;Conta EBITDA="RECEITA LIQUIDA"))

 

Without knowing the structure of your raw data it is hard to give precise support.

 

Best regards.

Thank  you for your help!

I´d already tried this way. 

Sorry about the model. I forgot to post it. The measure i created based on your suggestion respected the context and calculated the same value ou MOV YTD measure. Follow the diagram and the result of your suggestion.

I need, at this case, to repeat 63.368.009 at all lines to divide by.

 

Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA"))

 

Diagram.JPGResult.JPG

 

Hello there are two things you could try next:

 

Firstly, add ALL(dColigada) to your CALCULATE. I assume this is your second level row.

 

If this doesn't work you can try and build the Measures in two steps.

Step1:=Calculate([MOV YTD];Filter(...))

Step2:=Calculate([Step1];ALL(...);All(...))

 

Additionally you could highlight in your image which row is related to which row in your dRazzo.

And please show us the columns you selected for your Pivot.

 

Best regards

Firstly, add ALL(dColigada) to your CALCULATE. I assume this is your second level row.

 

My second level row is dRazao. I tried with dRazao and with dColigada, but failed both. It Shows always the same result.

 

Medida  = Calculate([MOV YTD];all(dRazao);all(dEBITDA);all(dCalendario);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA"))

 

or 

 

Medida  = Calculate([MOV YTD];all(dEBITDA);all(dCalendario);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA"))

 

Additionally you could highlight in your image which row is related to which row in your dRazzo.

Follow the visualization Pane image.

 

dRazao.Classeconta2 is related with dEbitda.Classeconta2 to show CONTA_EBITDA.

 

If this doesn't work you can try and build the Measures in two steps.

Step1:=Calculate([MOV YTD];Filter(...))

Step2:=Calculate([Step1];ALL(...);All(...))

 

Medida =

VAR STEP1 = Calculate([MOV YTD];Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA"))

RETURN CALCULATE(STEP1;ALL(dEBITDA);ALL(dRazao))

 

The same result.

 

SameReult.JPGVisualizationPane.JPG

It's tricky.

 

I see you have a separate table for your measures. Maybe this causes the problem.

 

Next try:

Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA");dEBITDA)

 

You can add a table as Filter in Calculate so the measure is calculated as it would be if you had calculated it in this table.

(I don't know if this is correct in grammar, but I hope it's understandable)

It's tricky.

I see you have a separate table for your measures. Maybe this causes the problem.

 

Yes..5 days trying and failing! I did it  according the good practices to organize it better, but if it is a issue i can join it all in dRazao.

 

Next try:

Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA");dEBITDA)

 

Same Result.

 

You can add a table as Filter in Calculate so the measure is calculated as it would be if you had calculated it in this table.

 

 I did not understand

 

tks

 

Wallace

I understand why you are on it for such a long time.

 

Maybe you can change the table

Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA");dRazao)

Maybe you can change the table

Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA");dRazao)

 

The number has changed but it is wrong.

 

After several attempts, I succeeded

 

%Vert RLiq =

VAR DEN_01 = CALCULATE([RL YTD];ALLSELECTED(dRazao); FILTERS(dColigada[Empresa_Simples]))
VAR NUM_01 = [MOV YTD]

RETURN
DIVIDE(NUM_01;DEN_01;0)

 

ok.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.