Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am looking for a tip regarding the below visual.
1. Does anyone know which visual it is (screen "Goal")? It doesn't seem to be a standard matrix.
2. How can I format various metrics in a different way (dollars, whole number, percentage) based on my dataset? Please note that I can pivot/unpivot columns - that is not a problem.
My ultimate goal is that I have various metrics in column "Metric" and some of them should be shown as a percentage, some other ones as value or as a dollar.
thank you
Goal:
My Matrix:
My dataset:
Business Unit | Account Desc. | Metric | Period | Scenario | Value |
YHZ | AGMAcqDiv | Acquire/Divest | Saturday, February 1, 2020 | Actual | 14 |
YYY | AGMAcqDiv | Acquire/Divest | Saturday, February 1, 2020 | Actual | 14 |
ZZZ | AGMAcqDiv | Acquire/Divest | Saturday, February 1, 2020 | Actual | 14 |
YHZ | AGMAcqDiv | Acquire/Divest | Wednesday, January 1, 2020 | Actual | 13 |
YYY | AGMAcqDiv | Acquire/Divest | Wednesday, January 1, 2020 | Actual | 13 |
ZZZ | AGMAcqDiv | Acquire/Divest | Wednesday, January 1, 2020 | Actual | 13 |
YHZ | AGMAcqDiv | Acquire/Divest | Sunday, March 1, 2020 | Actual | 13 |
YYY | AGMAcqDiv | Acquire/Divest | Sunday, March 1, 2020 | Actual | 13 |
ZZZ | AGMAcqDiv | Acquire/Divest | Sunday, March 1, 2020 | Actual | 13 |
YHZ | AGMAcqDiv | Acquire/Divest | Wednesday, April 1, 2020 | Actual | 13 |
YYY | AGMAcqDiv | Acquire/Divest | Wednesday, April 1, 2020 | Actual | 13 |
ZZZ | AGMAcqDiv | Acquire/Divest | Wednesday, April 1, 2020 | Actual | 13 |
YHZ | AGMAcqDiv | Acquire/Divest | Friday, May 1, 2020 | Actual | 13 |
YYY | AGMAcqDiv | Acquire/Divest | Friday, May 1, 2020 | Actual | 13 |
ZZZ | AGMAcqDiv | Acquire/Divest | Friday, May 1, 2020 | Actual | 13 |
ABCD | TradeAGM | AGM | Wednesday, January 1, 2020 | Actual | 4345 |
ABCD | TradeAGM | AGM | Saturday, February 1, 2020 | Actual | 4840 |
ABCD | TradeAGM | AGM | Sunday, March 1, 2020 | Actual | 4565 |
ABCD | TradeAGM | AGM | Wednesday, April 1, 2020 | Actual | 5418 |
ABCD | TradeAGM | AGM | Friday, May 1, 2020 | Actual | 4340 |
EFG | TradeAGM | AGM | Wednesday, January 1, 2020 | Actual | 1643 |
EFG | TradeAGM | AGM | Saturday, February 1, 2020 | Actual | 1892 |
EFG | TradeAGM | AGM | Sunday, March 1, 2020 | Actual | 1787 |
Solved! Go to Solution.
Hi @Anonymous ,
The visual in your "Goal" image looks like the standard matrix.
A single measure can only have a single data format. What you're seeing in that image is text formatting using the FORMAT() dax formula.
Take a quick look at this link:
https://docs.microsoft.com/sv-se/dax/pre-defined-numeric-formats-for-the-format-function
Essentially this relies on the fact that you have numeric values of different types calculated and then you use a SWITCH() together with several different FORMAT()s to define how you want your numbers to be displayed.
Hope this helps,
J
Hi @Anonymous ,
In addition, we can create a measure to replace your value to meet your requirement.
Measure Value =
FORMAT(
SUM('Table'[Value]),
SWITCH (
TRUE (),
MAX('Table'[Metric])="AGM%", "Percent",
MAX('Table'[Metric])="AGM","Currency",
MAX('Table'[Metric])="Acquire/Divest","General Number"
)
)
You can also refer to the post I answered earlier.
https://community.powerbi.com/t5/Desktop/How-to-create-a-visual-like-this/td-p/1084043
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
In addition, we can create a measure to replace your value to meet your requirement.
Measure Value =
FORMAT(
SUM('Table'[Value]),
SWITCH (
TRUE (),
MAX('Table'[Metric])="AGM%", "Percent",
MAX('Table'[Metric])="AGM","Currency",
MAX('Table'[Metric])="Acquire/Divest","General Number"
)
)
You can also refer to the post I answered earlier.
https://community.powerbi.com/t5/Desktop/How-to-create-a-visual-like-this/td-p/1084043
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
The visual in your "Goal" image looks like the standard matrix.
A single measure can only have a single data format. What you're seeing in that image is text formatting using the FORMAT() dax formula.
Take a quick look at this link:
https://docs.microsoft.com/sv-se/dax/pre-defined-numeric-formats-for-the-format-function
Essentially this relies on the fact that you have numeric values of different types calculated and then you use a SWITCH() together with several different FORMAT()s to define how you want your numbers to be displayed.
Hope this helps,
J
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |