CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Serdia
Serdia

Reputation: 4426

Applying numeric slicer gives incorrect values as a result

I created calculated table that would summarize data by ClaimNumber:

ByClaim = SUMMARIZE(fact_Losses
                        ,fact_Losses[AccidentDate]
                        ,fact_Losses[ClaimNumber]
                        ,fact_Losses[TransactionType]                       
                        ,"LossAmount", SUM(fact_Losses[PaymentAmount])
)

Outcome looks like this:

enter image description here

I generated another calculated table Cap Slicer that will be used as a slicer:

Cap Slicer = GENERATESERIES(10000,MAX(fact_Losses[LossPerClaim])+10000,10000)

enter image description here

Now I need to decrease the amount of LossAmount for each ClaimNumber by value chooses in Cap Slicer table. For that I wrote a measure:

Capped Loss Amount1 = 
VAR MaxLoss = MAX('Cap Slicer'[Value])
RETURN
   SUMX(
        ByClaim,
        IF(ByClaim[LossAmount]  > MaxLoss,
        MaxLoss,
        ByClaim[LossAmount] 
        ))

When choosing value $1,000,000 in Cap Slicer I'm expecting to see Capped Loss Amount1 value of 1,000,000 for ClaimNumber USGL00000000103.

But for some reason it gives me 1,002,737.

Where that 2,737 comes from?

enter image description here

I am using SUMX function to iterate one row at a time. Is there other function for that that I am not aware of?

Thank you

Upvotes: 1

Views: 141

Answers (1)

Olly
Olly

Reputation: 7891

Table ByClaim clearly has more than one row for claim number USGL00000000103. Your measure applies the cap per row, not per claim.

If you want the cap to apply per claim, then change your measure to:

Capped Loss Amount1 = 
VAR MaxLoss = 
    MAX('Cap Slicer'[Value])
VAR ActualLoss = 
   SUM( ByClaim[LossAmount] )
RETURN
    MIN ( MaxLoss, ActualLoss)

Upvotes: 3

Related Questions