Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
Not sure if this can be achieved but I have 3 metrics, sales, margin and qty. Currently these are seperate metrics and I was wondering if these can be in one metric using variables?
Thanks
Alex
Solved! Go to Solution.
Hi @villa1980,
Yes, you can combine calculations for sales, margin, and quantity into a single measure using variables. However, keep in mind that a measure in DAX always returns a single scalar value, so you'll need to decide whether you want to return one of the metrics based on a condition (such as a slicer selection) or concatenate them into a text string.
For example, if you want the user to choose which metric to display using a slicer, you could create a measure like this:
Combined Metric =
VAR SalesMetric = [Sales]
VAR MarginMetric = [Margin]
VAR QtyMetric = [Qty]
VAR SelectedMetric = SELECTEDVALUE('Metric Selector'[Metric]) // a table with options "Sales", "Margin", "Qty"
RETURN
SWITCH(
SelectedMetric,
"Sales", SalesMetric,
"Margin", MarginMetric,
"Qty", QtyMetric,
SalesMetric // default to Sales if nothing is selected
)
Alternatively, if you want to display all three metrics in one card visual as a concatenated string, you could do something like:
Combined Metric Text =
VAR SalesMetric = FORMAT([Sales], "#,##0")
VAR MarginMetric = FORMAT([Margin], "0.00%")
VAR QtyMetric = FORMAT([Qty], "#,##0")
RETURN
"Sales: " & SalesMetric & " | Margin: " & MarginMetric & " | Qty: " & QtyMetric
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
Hi @villa1980
That's possible but you would need another table containing a column that holds the metric names, a sort column and a format string. Just variables without a row that you can assign the values to won't work.
In the measure below, you can use the actual expressions but I prefer putting them in their own measures for re-usability.
My Metrics =
VAR _Rev = [Total Revenue]
VAR _TXn = [Total Transactions]
VAR _ATR = [Average Rev]
RETURN
SWITCH (
SELECTEDVALUE ( Metrics[Metric] ),
"Revenue", _Rev,
"Transactions", _TXn,
"ATR", _ATR
)
The Format String column in the Metrics table will be used to apply the desired formatting to each metric. The above screnshot shows #,#.00 instead of different format for each. While you can use FORMAT function, that will return a text string instead of a number.
If this is the only thing you're trying to achieve, I would just use Field Parameters intead which is easier to setup and automated. You can access this feature from the Modeling tab, New Parameter then Fields. This creates a calculated table which you can edit if you need to rename the Display Names, chang the sort, add and or delete a measure.
Please see the attached sample pbix.
Hi @villa1980
That's possible but you would need another table containing a column that holds the metric names, a sort column and a format string. Just variables without a row that you can assign the values to won't work.
In the measure below, you can use the actual expressions but I prefer putting them in their own measures for re-usability.
My Metrics =
VAR _Rev = [Total Revenue]
VAR _TXn = [Total Transactions]
VAR _ATR = [Average Rev]
RETURN
SWITCH (
SELECTEDVALUE ( Metrics[Metric] ),
"Revenue", _Rev,
"Transactions", _TXn,
"ATR", _ATR
)
The Format String column in the Metrics table will be used to apply the desired formatting to each metric. The above screnshot shows #,#.00 instead of different format for each. While you can use FORMAT function, that will return a text string instead of a number.
If this is the only thing you're trying to achieve, I would just use Field Parameters intead which is easier to setup and automated. You can access this feature from the Modeling tab, New Parameter then Fields. This creates a calculated table which you can edit if you need to rename the Display Names, chang the sort, add and or delete a measure.
Please see the attached sample pbix.
Hi @villa1980,
Yes, you can combine calculations for sales, margin, and quantity into a single measure using variables. However, keep in mind that a measure in DAX always returns a single scalar value, so you'll need to decide whether you want to return one of the metrics based on a condition (such as a slicer selection) or concatenate them into a text string.
For example, if you want the user to choose which metric to display using a slicer, you could create a measure like this:
Combined Metric =
VAR SalesMetric = [Sales]
VAR MarginMetric = [Margin]
VAR QtyMetric = [Qty]
VAR SelectedMetric = SELECTEDVALUE('Metric Selector'[Metric]) // a table with options "Sales", "Margin", "Qty"
RETURN
SWITCH(
SelectedMetric,
"Sales", SalesMetric,
"Margin", MarginMetric,
"Qty", QtyMetric,
SalesMetric // default to Sales if nothing is selected
)
Alternatively, if you want to display all three metrics in one card visual as a concatenated string, you could do something like:
Combined Metric Text =
VAR SalesMetric = FORMAT([Sales], "#,##0")
VAR MarginMetric = FORMAT([Margin], "0.00%")
VAR QtyMetric = FORMAT([Qty], "#,##0")
RETURN
"Sales: " & SalesMetric & " | Margin: " & MarginMetric & " | Qty: " & QtyMetric
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
User | Count |
---|---|
73 | |
69 | |
36 | |
26 | |
24 |
User | Count |
---|---|
96 | |
92 | |
54 | |
45 | |
41 |