Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
3 years ago
Solved

Formatting difference between two numbers

Hello,

 

I'm trying to use a measure to calculate the difference between last years and this year spend and format it to show the data as thousands, millions etc. 

 

I'm using the following measure:

Payment YoY = 

VAR PaymentCurrentY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2023"))
VAR PaymentPreviousY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2022"))
VAR YoYAmount =PaymentCurrentY - PaymentPreviousY
RETURN
     SWITCH(TRUE (),
        YoYAmount< 1000 && NOT(ISBLANK(YoYAmount)), FORMAT(YoYAmount,"$0.0"),
        YoYAmount >= 1000 && YoYAmount < 1000000 , CONCATENATE ( FORMAT(YoYAmount / 1000,"$0.0"), "k" ),
        YoYAmount >= 1000000 && YoYAmount < 1000000000 ,  CONCATENATE ( FORMAT(YoYAmount / 1000000,"$0.0"),"M"  ),
        YoYAmount >= 1000000000 && YoYAmount <= 1000000000000 ,  CONCATENATE (FORMAT(YoYAmount/1000000000,"$0.0"), "B" ) )
 
However, this doesn't seem to work. When I applied the same switch statement for formatting on just this years spend (without subtracting the previous from the current) the formatting works like a charm:
 

Payment 2023= 

VAR PaymentCurrentY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2023"))
VAR PaymentPreviousY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2022"))
VAR YoYAmount =PaymentCurrentY
RETURN
     SWITCH(TRUE (),
        YoYAmount< 1000 && NOT(ISBLANK(YoYAmount)), FORMAT(YoYAmount,"$0.0"),
        YoYAmount >= 1000 && YoYAmount < 1000000 , CONCATENATE ( FORMAT(YoYAmount / 1000,"$0.0"), "k" ),
        YoYAmount >= 1000000 && YoYAmount < 1000000000 ,  CONCATENATE ( FORMAT(YoYAmount / 1000000,"$0.0"),"M"  ),
        YoYAmount >= 1000000000 && YoYAmount <= 1000000000000 ,  CONCATENATE (FORMAT(YoYAmount/1000000000,"$0.0"), "B" ) )
 

 Why does the formatting not work when I try to apply it on the current spend - previous spend? I checked and the YoYAmount is a number, so it should be the same. 

 

Thank you for your help!

Need Help
  • Wilson_'s avatar
    Wilson_
    3 years ago

    Hello,

     

    All your YoY values are negative, and therefore are all less than 1,000. The implicit assumption in your SWITCH statement is that YoYAmount will always be positive. Therefore, you need to wrap all your YoYAmount variables in the SWITCH statement in an ABS function.


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

2 Replies