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 currently have my data structured in the following manner:
I am having trouble calculating within one column. For example, in my column KPI i have:
Product 1 Revenue
Product 2 Revenue
I am trying to calculate the Revenue Mix: Product 1 Revenue/ (Product 1 Revenue+Product 2 Revenue). Is there any way to create a measure that calculates this?
I tried to use the If function for KPI, in order to filter out the value, however, since the same KPI appears more than once in the table it tells me that I need one unique value. However, there is no way i can Sum (or any calculation of the sort) a string.
Thank you,
Pamela
Solved! Go to Solution.
Hi Pamela - you have a couple of options here.
1. When importing the data to Power BI, you could 'pivot' the data to make each KPI a column. This is not best practice if there are multiple KPI(s) to calculate. This is due to an increase in the number of columns and an increase in cardinality in values - thus higher memory consumption and slower calculations. If you didn't have very many KPI(s) and not a bunch of rows, you would pivot the data and sum the columns as your calculation - i.e. Revenue - Product 1 = SUM( [Product 1 Revenue]), Revenue - Product 2 = SUM( [Product 2 Revenue]) with and end result measure of Revenue Mix = DIVIDE( [Revenue - Product 1], [Revenue - Product 1] + [Revenue - Product 2], 0)
2. When your values are in rows vs columns, you have to use a CALCULATE function. For this calculation in particular, you could
a) create measures for each KPI - i.e. Revenue - Product 1 = CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue"), Revenue - Product 2 = CALCULATE( SUM( [Value]), [KPI] = "Product 2 Revenue") - if those are required to be viewed individually - and an end result measure - i.e. Revenue Mix = DIVIDE( [Revenue - Product 1], [Revenue - Product 1] + [Revenue - Product 2], 0)
b) create your end result measure w/o intermediate measures - i.e. Revenue Mix = DIVIDE( CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue"), CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue" || [KPI] = "Product 2 Revenue"), 0)
Hi Pamela - you have a couple of options here.
1. When importing the data to Power BI, you could 'pivot' the data to make each KPI a column. This is not best practice if there are multiple KPI(s) to calculate. This is due to an increase in the number of columns and an increase in cardinality in values - thus higher memory consumption and slower calculations. If you didn't have very many KPI(s) and not a bunch of rows, you would pivot the data and sum the columns as your calculation - i.e. Revenue - Product 1 = SUM( [Product 1 Revenue]), Revenue - Product 2 = SUM( [Product 2 Revenue]) with and end result measure of Revenue Mix = DIVIDE( [Revenue - Product 1], [Revenue - Product 1] + [Revenue - Product 2], 0)
2. When your values are in rows vs columns, you have to use a CALCULATE function. For this calculation in particular, you could
a) create measures for each KPI - i.e. Revenue - Product 1 = CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue"), Revenue - Product 2 = CALCULATE( SUM( [Value]), [KPI] = "Product 2 Revenue") - if those are required to be viewed individually - and an end result measure - i.e. Revenue Mix = DIVIDE( [Revenue - Product 1], [Revenue - Product 1] + [Revenue - Product 2], 0)
b) create your end result measure w/o intermediate measures - i.e. Revenue Mix = DIVIDE( CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue"), CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue" || [KPI] = "Product 2 Revenue"), 0)
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |