Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm loading data from a datasource and using MATRIX for output. I need some values to be displayed in decimal numbers and some values to be displayed as %'s. Is this possible?
Solved! Go to Solution.
Hi @Anonymous ,
Please refer to my .pbix file.
1. “I need only totals as totals of Actuals”
For this requirement, you can create a measure like this.
Measure =
VAR x =
SWITCH(
SELECTEDVALUE(Sales[CountryRegion]),
"dd", FORMAT( SUM(Sales[Sale 2014]), "Percent" ),
FORMAT( SUM(Sales[Sale 2014]), "#, 0.00")
)
VAR y =
CALCULATE(
SUM(Sales[Sale 2014]),
FILTER( ALL(Sales), Sales[CountryRegion] = MAX(Sales[CountryRegion]) && Sales[Month] = "December")
)
RETURN
IF(
HASONEFILTER(Sales[Month]),
x,y
)
2. Show the border of "Variance"
This requirement is currently not possible, but you can mark ‘Variance’ in other ways.
Firsr, create a measure like this.
Conditional format =
IF(
MAX(Sales[Month]) = "January",
"Green"
)
Second, add the measure to the 'conditional formatting' label.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous - Very dependent on what you are doing, if the two different formats are different measures/columns or not. You may find some success using FORMAT but that turns things to text. The technique here might also help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
@Anonymous
It depends if you need to have two different formats for the same measure, you can try like the example below.
Total Orders =
VAR C = COUNTROWS(Orders)
RETURN
IF(
C < 5 ,
FORMAT(COUNTROWS(Orders)/100,"0.0%"),
C
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy ,
Thanks for your response.
My dataset is as shown below:
Allocations | m$ |
Actual | 0.08 |
Strategic | 0.03 |
Variance | 0.12 |
Funds | 3456.08 |
My output is a MATRIX and needs to be displayed as follows:
Actual | Strategic | Variance | Funds |
8% | 3% | 12% | 3456.08 |
@Anonymous
You can modify the SWITCH statement to suit for your options.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy,
Thanks a lot. That works !
My values do come up as you've mentioned. But my % totals now add up, instead I need it to be displayed as follows:
Bank | Funds | |
Actuals | 8% | |
Strategic | 3% | |
Variance | 5% | 545 |
Cash | ||
Actuals | 3% | |
Strategic | 4% | |
Variance | 2% | 452 |
Total | 25% |
I'm using the formula you'd mentioned below to get this displayed as % for Actual, Strategic and Variance and decimal for Funds
But my total needs to only add totals(Actuals)
And also, my totals come up in Bank and Cash. (I'm using MATRIX in Power BI)
@Anonymous
Can you share your sample data?
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
HI @Fowmy
This is the best I can provide:
My source Data
My required output
This is what I've done in Power BI
My Measure value2 has the formula you mentioned:
I dont need the totals in Propert, Cash , DSG and Long-Term. Also, is there a way I can highlight the borders of Variance alone as shown in the required output?
ara
@Anonymous
Try this measure:
Measure =
SWITCH(
SELECTEDVALUE(DATA[Invest]),
"FUM", FORMAT( SUM(DATA[m$]), "Currency"),
FORMAT( SUM(DATA[m$]), "Percent")
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
When I used the formula you had mentioned, I still get values in Cash, DSG and Long-Term
HI @Fowmy
Using the measure, the total istill calculates total of everything. I need only totals as totals of Actuals
As per my future state diagram
My grand totals are 8.0, 99.4 .. But in the screenshot you had provided, it calculates total of everything.
ara
Hi @Anonymous ,
Please refer to my .pbix file.
1. “I need only totals as totals of Actuals”
For this requirement, you can create a measure like this.
Measure =
VAR x =
SWITCH(
SELECTEDVALUE(Sales[CountryRegion]),
"dd", FORMAT( SUM(Sales[Sale 2014]), "Percent" ),
FORMAT( SUM(Sales[Sale 2014]), "#, 0.00")
)
VAR y =
CALCULATE(
SUM(Sales[Sale 2014]),
FILTER( ALL(Sales), Sales[CountryRegion] = MAX(Sales[CountryRegion]) && Sales[Month] = "December")
)
RETURN
IF(
HASONEFILTER(Sales[Month]),
x,y
)
2. Show the border of "Variance"
This requirement is currently not possible, but you can mark ‘Variance’ in other ways.
Firsr, create a measure like this.
Conditional format =
IF(
MAX(Sales[Month]) = "January",
"Green"
)
Second, add the measure to the 'conditional formatting' label.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Has your problem been solved?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |