Forum Discussion
Sum IF or something else
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.
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
3 Replies
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
Thanks for the shout out jdbuchanan71!
Oh had no idea about this. Thank you for the help - now i gotta go play with formatting 🙂
