Forum Discussion

101Mathew's avatar
101Mathew
Icon for Advocate V rankAdvocate V
6 years ago
Solved

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
General Comment
Need Help
Tutorial Requests
  • 101Mathew's avatar
    101Mathew
    6 years ago

    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

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    6 years ago

    101Mathew 

     

    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]))
     
     

     

     

  • 101Mathew's avatar
    101Mathew
    Icon for Advocate V rankAdvocate V
    6 years ago

    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