cancel
Showing results for
Did you mean:
Resolver II

## DAX - Why do I get 100%

Hi All,

I'm starting to understand DAX more - and as soon as I think I'm getting there something like this pops up

I need to show how much business CS brokers have had via wholesale arm of the business.  So I wrote this (trying to use VAR when I can)

CS as % of Wholesale = VAR WholesaleAmount =
CALCULATE (
[T GWP],
FILTER (
'dDivision etc',
'dDivision etc'[Division] = "Wholesale"
)
)
VAR CSamountInWholesale =
CALCULATE (
WholesaleAmount,
FILTER ( dWholesaleBrokers, dWholesaleBrokers[CS/EXT] = "CS" )
)
VAR CSasPerOfWholesale =
ROUND ( DIVIDE ( CSamountInWholesale, WholesaleAmount ), 4 )
VAR Result = CSasPerOfWholesale
RETURN
Result
1 ACCEPTED SOLUTION
Resolver II

Hi All,

firstly thank you @negi007 , for your input.

I found the error in my DAX.  I was using a measure as the value taken in, after replacing this is an explict calculation of the fact table values, the below works like a charm.

My question now is why.  Is this something to do with context transition?

Solution to original issue below:

CS as % of Wholesale =
VAR WholesaleAmount =
CALCULATE (
SUM ( fTransactions[GWP] ) + SUM ( fTransactions[FEES] )
+ SUM ( fTransactions[ASS PRODS] )
+ SUM ( fTransactions[LEGAL] ),
FILTER ( 'dDivision etc', 'dDivision etc'[Division] = "Wholesale" )
)
VAR CSamountInWholesale =
CALCULATE (
SUM ( fTransactions[GWP] ) + SUM ( fTransactions[FEES] )
+ SUM ( fTransactions[ASS PRODS] )
+ SUM ( fTransactions[LEGAL] ),
FILTER ( dWholesaleBrokers, dWholesaleBrokers[CS/EXT] = "CS" )
)
VAR CSasPerOfWholesale =
ROUND ( DIVIDE ( CSamountInWholesale, WholesaleAmount ), 4 )
VAR Result = CSasPerOfWholesale
RETURN
Result

2 REPLIES 2
Resolver II

Hi All,

firstly thank you @negi007 , for your input.

I found the error in my DAX.  I was using a measure as the value taken in, after replacing this is an explict calculation of the fact table values, the below works like a charm.

My question now is why.  Is this something to do with context transition?

Solution to original issue below:

CS as % of Wholesale =
VAR WholesaleAmount =
CALCULATE (
SUM ( fTransactions[GWP] ) + SUM ( fTransactions[FEES] )
+ SUM ( fTransactions[ASS PRODS] )
+ SUM ( fTransactions[LEGAL] ),
FILTER ( 'dDivision etc', 'dDivision etc'[Division] = "Wholesale" )
)
VAR CSamountInWholesale =
CALCULATE (
SUM ( fTransactions[GWP] ) + SUM ( fTransactions[FEES] )
+ SUM ( fTransactions[ASS PRODS] )
+ SUM ( fTransactions[LEGAL] ),
FILTER ( dWholesaleBrokers, dWholesaleBrokers[CS/EXT] = "CS" )
)
VAR CSasPerOfWholesale =
ROUND ( DIVIDE ( CSamountInWholesale, WholesaleAmount ), 4 )
VAR Result = CSasPerOfWholesale
RETURN
Result

Super User

You can create a measure like below to calculate the contribution% of a channel out of total business value

WS% = CALCULATE(SUM('Channel_Contb%'[Value]),'Channel_Contb%'[Channel]="Wholesale")/CALCULATE(SUM('Channel_Contb%'[Value]),ALLSELECTED('Channel_Contb%'[Channel]))

Proud to be a Super User!

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.