Tableau to Power BI Migration

[kapildua16]

Hi Everyone, I am working on Migrating a Tableau Project to Power BI

In Tableau there is a fixed Lod name currentpremium
{Fixed[Country],[ClientID]:Min([Premium])}

for this i writo below dax

CALCULATE(
MIN('Table'[Premium]),
ALLEXCEPT(
'Table',
'YourTable'[Country],
'YourTable'[ClientID],
))

now tableau is using a new lod on top of currentpremium
Calc__Q2=
{Fixed[Country],[Clientid]:Percentile([currentpremium],0.5)}

How can i replicate this in power bi.
i am trying to use allexcept but inside percentile i am not able to use measure name

and while placing this calculation in tableau, it is performing average
so my power bi should also perform average in calculation

[kapildua16]

@bhanu_gautam - Thanks for Providing the solution but i am not getting the correct result

I am working on a calculation that involves computing the average of percentiles based on grouped data. I am able to get the correct result (16948) by creating separate DAX-calculated tables, but I am unable to achieve the same result using measures.

Here’s what I did that worked:

1. I created a table calc_currentPremium using the following DAX: Calc_currenrrate =
   SUMMARIZE(
   'Plan',
   'Plan'[Country],
   'Plan'[ClientID],
   'Plan'[Producttype],
   'Plan'[PlanDesignYear],
   "mincurrentPremium", MIN('Plan'[Currentrate_premium])
   )
   On top of this, I created another table Q2 to calculate the median:

Q2 =
SUMMARIZE(
Calc_currenrrate,
Calc_currenrrate[Country],
Calc_currenrrate[Producttype],
Calc_currenrrate[PlanDesignYear],
"Median", PERCENTILE.INC(Calc_currenrrate[mincurrentPremium], 0.5)
)

Finally, I created a measure to calculate the average of medians:

AvergareQ2 = AVERAGE(Q2[Median])

This approach gives me the correct result.

However, when I try to implement the same logic entirely as measures without creating intermediate tables, the result is incorrect

MinCurrentRate =
CALCULATE(
MIN('Plan'[Currentrate_premium]),
ALLEXCEPT(
'Plan',
'Plan'[Country],
'Plan'[ClientID],
'Plan'[Producttype],
YEAR('Plan'[PlanDesignEffectiveDate])
)
)

MedianCurrentRate =
PERCENTILEX.INC(
SUMMARIZE(
'Plan',
'Plan'[Country],
'Plan'[Producttype],
YEAR('Plan'[PlanDesignEffectiveDate]),
"MinRate", [MinCurrentRate]
),
[MinRate],
0.5
)

AverageMedianRate =
AVERAGEX(
SUMMARIZE(
'Plan',
'Plan'[Country],
'Plan'[Producttype],
YEAR('Plan'[PlanDesignEffectiveDate]),
"Median", [MedianCurrentRate]
),
[Median]
)

[Anonymous]

Hi @kapildua16 ,
Based on your description, the fact that the same logic works for the calculation table but not for the MEASURE may be due to the different contexts they are applied to. In tables or matrices, the columns used in the measure that are missing from the visualization may also result in a different context. If you want more accurate help, you can provide full example data or pbix files and expected results so we can help you faster. Please hide sensitive information in advance.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[bhanu_gautam]

@kapildua16 , First create a measure for 

currentpremium =
CALCULATE(
MIN('Table'[Premium]),
ALLEXCEPT('Table', 'Table'[Country], 'Table'[ClientID])
)

create a measure for Calc__Q2 which calculates the 50th percentile (median) of currentpremium

Calc__Q2 =
CALCULATE(
PERCENTILEX.INC(
SUMMARIZE(
'Table',
'Table'[Country],
'Table'[ClientID],
"currentpremium", [currentpremium]
),
[currentpremium],
0.5
),
ALLEXCEPT('Table', 'Table'[Country], 'Table'[ClientID])
)

if you need to perform an average calculation on top of Calc__Q2, you can create another measure:

Average_Calc__Q2 =
AVERAGEX(
SUMMARIZE(
'Table',
'Table'[Country],
'Table'[ClientID],
"Calc__Q2", [Calc__Q2]
),
[Calc__Q2]
)