Tableau LOD Fixed to Power BI

[danni1979]

Hi All,

We are really struggling converting Tableau reports to Power BI where the FIXED function is used. I can get the correct value at detail level but i can't get a correct total.

Below is a test table, i want the amount owed for each unique debt id however i then want the total.

the tableau function is Tableau formula is { FIXED [Debt ID]: MAX([Amount Owed])}

Debt ID	Amount Owed	Client	Product
123	500.00	client  1	product 1
123	500.00	client  1	product 2
123	500.00	client  1	product 3
124	200.00	client  1	product 1
124	200.00	client  1	product 2
125	100.00	client  1	product 2

In Power BI I managed to get the below using formula 

fixed amount owed = CALCULATE(MAX(Test[Amount Owed]),ALLEXCEPT(Test,Test[Debt ID]))

The correct value is returned at debt id level however the total is just showing the maximum amount owed. I need the total to reflect the sum of the 'fixed amount owed'. If I add this to a visual it will just show $500 instead of $800.

Debt ID	fixed amount owed
123	500.00
124	200.00
125	100.00
Total	500.00

We use the FIXED function in tableau in all our reports and I have tried everything I could find on-line to resolve this with no luck.

Could really do with some detailed help thanks 

[parry2k · Accepted solution]

@danni1979 this measure will do it, change column name and table name as per your model

Amount Owed Sum = 
SUMX ( 
    VALUES ( 'Table'[Debt ID] ), 
    CALCULATE (
        MAX ('Table'[Amount Owed] ),
        ALLEXCEPT ('Table','Table'[Debt ID] )
    ) 
)

[danni1979]

Thanks, will try this today on our more complex data set and let you know how it goes.