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

[Anonymous]

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)

 

[Anonymous]

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

[Anonymous]

I have this error

[Anonymous]

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

Can I send it to you by email?

[lbendlin]

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

[Anonymous]

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.

 

[lbendlin]

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

[lbendlin]

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