Sum IF or something else

[Mihail_C]

Hello community,

I am new to PowerBI and still struggling to understand what would be the best solution for my problem.

I have a table that holds a report type which is text value ('P&L', 'CAPEX', 'ASSET') and a budget column that holds numerical values.

We used for now data for the first two report types until now and the budget was just a measure that divided by 1000 to keep the numbers low and more easily readable.

Budget_K = sum('FinMgmt_FY23'[Budget])/1000

Now, after inserting ASSET data, which is basically asset count, not asset cost, the report is not looking as great. I am trying to see what DAX function would be more appropriate to show [Budget])/1000 when the report type is ('P&L', 'CAPEX') and to show just [Budget] value when report type is 'ASSET'.

The report is now filtered based on report type via a slicer.

I tried to check an IF clause but is not working for me because the report type is not a measure so i cant select it in IF clause.

Any help would be appreciated.

Thank you

[Mihail_C]

Oh had no idea about this. Thank you for the help - now i gotta go play with formatting 🙂

[parry2k]

Thanks for the shout out @jdbuchanan71!

[jdbuchanan71 · Accepted solution]

@Mihail_C 

You can use the new preview feature for 'Dynamic format string for measures' to accomplish what you are looking for. 
Go to File > Options and settings > Options and enable the feature:

There is more information about the feature here:  https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings

Then you have a measure that sums up your budget amount and also has the dynamic formatting code.

Measure:

Budget Amount = SUM ( FinMgmt_FY23[Budget] )

Format:

SWITCH (
    SELECTEDVALUE ( FinMgmt_FY23[Report Type] ),
    "ASSET", "#,0",
    "#,0,. K"
)

Here is what the formatted measure looks like vs the raw data:

I have attached my sample file for you to look at. 

Finally, a shout out to @parry2k  for his excellent video on number formatting using the new feature.  https://www.youtube.com/watch?v=tTNkPesnx5M