topic Re: Dax measure to divide some row lines to specific row lines, in a Matrix in DAX Commands and Tips

Dax measure to divide some row lines to specific row lines, in a Matrix

Anonymous — Fri, 03 Jun 2022 09:48:46 GMT

Hello everyone,

I am trying to have a dax measure in a matrix, to divide all the rows to some specific rows.

I have 3 levels of hierarchy: and every row ( from the last hierarchy level) should be divided by two rows from the firs level of hierarchy.

I tried to make something like this, with the help of you, but It only works when I have only the first level of hierarcy in the matrix:

% of CAB =

VAR Lvl1 = SELECTEDVALUE('PL Transactions_V'[Level 1])

VAR CABCAN =

IF(Lvl1 <= "07",

CALCULATE(SUM('PL Transactions_V'[Amount]),'PL Transactions_V'[Level 1]="01. CAB- CIFRA DE AFACERI BRUTA"),

CALCULATE(SUM('PL Transactions_V'[Amount]),'PL Transactions_V'[Level 1]="06. CAN - CIFRA DE AFACERI NETA")

)

VAR _Result =

DIVIDE(SUM('PL Transactions_V'[Amount]),CABCAN)

RETURN

IF(NOT( ISBLANK(Lvl1)), _Result)

Re: Dax measure to divide some row lines to specific row lines, in a Matrix

lbendlin — Sun, 05 Jun 2022 02:22:58 GMT

You cannot influence dataset data from measures.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Dax measure to divide some row lines to specific row lines, in a Matrix

Anonymous — Tue, 07 Jun 2022 06:11:44 GMT

Hello! I hope thi is ok.

In the first capture I have column "% of CAB working"

In the second capture, when I drill down to Level 2, 3 or 4, the measure is not calculating for them.

Re: Dax measure to divide some row lines to specific row lines, in a Matrix

lbendlin — Tue, 07 Jun 2022 22:29:49 GMT

No, not ok. Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Dax measure to divide some row lines to specific row lines, in a Matrix

Anonymous — Wed, 08 Jun 2022 06:34:54 GMT

The page is not leting me attach data table, or excel files.

Can I send it to you by email?

Re: Dax measure to divide some row lines to specific row lines, in a Matrix

lbendlin — Wed, 08 Jun 2022 11:40:00 GMT

The included link has instructions how to paste data into a table in your post.

Re: Dax measure to divide some row lines to specific row lines, in a Matrix

Anonymous — Wed, 08 Jun 2022 12:52:03 GMT

I have this error

Re: Dax measure to divide some row lines to specific row lines, in a Matrix

Anonymous — Wed, 15 Jun 2022 12:58:02 GMT

Half Resolved with All function:

% of CAB Canal CY =

VAR Lvl1 = SELECTEDVALUE('PL Transactions Canal_Vanzari_V'[Level 1])

VAR CABCAN =

IF(Lvl1 <= "07",

CALCULATE('Measures (2)'[Amount Canal Vanzari],'PL Transactions Canal_Vanzari_V'[Level 1]="01. CAB- CIFRA DE AFACERI BRUTA",ALL('PL Transactions Canal_Vanzari_V'[Level 0], 'PL Transactions Canal_Vanzari_V'[Level 1],'PL Transactions Canal_Vanzari_V'[Level 2],'PL Transactions Canal_Vanzari_V'[Level 3])),

CALCULATE('Measures (2)'[Amount Canal Vanzari],'PL Transactions Canal_Vanzari_V'[Level 1]="06. CAN - CIFRA DE AFACERI NETA",ALL('PL Transactions Canal_Vanzari_V'[Level 0],'PL Transactions Canal_Vanzari_V'[Level 1], 'PL Transactions Canal_Vanzari_V'[Level 2],'PL Transactions Canal_Vanzari_V'[Level 3]))

)

VAR _Result =

DIVIDE('Measures (2)'[Amount Canal Vanzari],CABCAN)

RETURN

IF(NOT( ISBLANK(Lvl1)), _Result)

Now is working to calculate for level > 1, but not <1