Hello Everyone! I am having an issue when it comes to using the DAX Function for TOPN and "Others".
The DAX Code is below for reference;
What I am trying to do is:
1. Have a List of Top 25 Billing Providers by Billed Amount, Allowed Amount, and Discount Percentage
2. Have a "others" subcategory to account for all other billing providers outside the top 25 list
3. The Discount should be composite and be calculated as Discount = 1 - (Allowed Amount / Billed Amount)
The issue is with the last step. Each Time I try calculating the discount I will get all of the providers outside the Top 25 Listed (pictures provided below). When I simply do the calculation Allowed Amount / Billed Amount I will get the function to group the way I wanted (Top 25 + "Others"). But for whatever reason when I do the composite it will result in retrieving everything outside the Top 25. I feel like I am so close! What am I doing wrong?
Calc. Discount =
IF (
ISINSCOPE ( 'Product Ranking'[Ranking group] ),
VAR NumOfProducts = 25
VAR RankingGroup =
SELECTEDVALUE ( 'Product Ranking'[Ranking group] )
VAR TopProducts =
TOPN ( NumOfProducts, ALLSELECTED ( 'Product Ranking' ), [Total Allowed Amount] )
RETURN
SWITCH (
RankingGroup,
"Billing Provider Name",
CALCULATE ( (1-[Measure Discount]), KEEPFILTERS ( TopProducts ) ),
"Others",
IF (
NOT ISINSCOPE ( 'Product Ranking'[Ranking Name] ),
VAR TopAmount =
CALCULATE ( (1-[Measure Discount]), TopProducts )
VAR AllAmount =
CALCULATE ( (1-[Measure Discount]), ALLSELECTED ( 'Product Ranking' ) )
VAR OtherAmt = AllAmount - TopAmount
RETURN
OtherAmt
)
),
[Total Allowed Amount]
)
Calc. Allowed Amount =
IF (
ISINSCOPE ( 'Product Ranking'[Ranking group] ),
VAR NumOfProducts = 25
VAR RankingGroup =
SELECTEDVALUE ( 'Product Ranking'[Ranking group] )
VAR TopProducts =
TOPN ( NumOfProducts, ALLSELECTED ( 'Product Ranking' ), [Total Allowed Amount] )
RETURN
SWITCH (
RankingGroup,
"Billing Provider Name",
CALCULATE ( [Total Allowed Amount], KEEPFILTERS ( TopProducts ) ),
"Others",
IF (
NOT ISINSCOPE ( 'Product Ranking'[Ranking Name] ),
VAR TopAmount =
CALCULATE ( [Total Allowed Amount], TopProducts )
VAR AllAmount =
CALCULATE ( [Total Allowed Amount], ALLSELECTED ( 'Product Ranking' ) )
VAR OtherAmt = AllAmount - TopAmount
RETURN
OtherAmt
)
),
[Total Allowed Amount]
)
Calc. Billed Amount =
IF (
ISINSCOPE ( 'Product Ranking'[Ranking group] ),
VAR NumOfProducts = 25
VAR RankingGroup =
SELECTEDVALUE ( 'Product Ranking'[Ranking group] )
VAR TopProducts =
TOPN ( NumOfProducts, ALLSELECTED ( 'Product Ranking' ), [Total Allowed Amount] )
RETURN
SWITCH (
RankingGroup,
"Billing Provider Name",
CALCULATE ( [Total Billed Amount], KEEPFILTERS ( TopProducts ) ),
"Others",
IF (
NOT ISINSCOPE ( 'Product Ranking'[Ranking Name] ),
VAR TopAmount =
CALCULATE ( [Total Billed Amount], TopProducts )
VAR AllAmount =
CALCULATE ( [Total Billed Amount], ALLSELECTED ( 'Product Ranking' ) )
VAR OtherAmt = AllAmount - TopAmount
RETURN
OtherAmt
)
),
[Total Billed Amount]
)
