cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
101Mathew
Resolver II
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
101Mathew
Resolver II
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

View solution in original post

2 REPLIES 2
101Mathew
Resolver II
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

View solution in original post

negi007
Super User
Super User

@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]))
 
 
negi007_0-1600699154590.png

 

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors