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 Guys,
I've been working with Tableau for the last 6 years and now I'm trying to use Power BI. I'm trying to do a table that is actually really simple but I haven't got the results I want.
So I have a database with about 40millions rows and 20 columns and 3 hierachy for measures and I have some indicator that is calculated on the database and others that I have to calculate as a measure in Power BI.
Client | Indicator | Value | A | B |
123 | A | 20 | ||
456 | A | 10 | ||
123 | B | 20 | ||
456 | B | 30 | ||
123 | C | 20 | 20 | |
456 | C | 10 | 30 |
Here I have 2 different clients and the indicator A and B is calculated on the database and the indicator C would be sum(A)/sum(B). To help me out, this informations come at the same row as indicator C and the indicator C come because its in the hierarchy on database. What I'm trying to reach here is this result:
Indicator | Value | C | Final |
A | 30 | 30 | |
B | 50 | 50 | |
C | 0,6 | 0,6 |
So first I need to calculate the indicators in Power BI and then mix the Value column with my calculated measure. So what I did using Tableau was
Solved! Go to Solution.
Hi @CaioYo ,
You could create a measure by the following formula:
First : enter a new table
Then create a measure:
Final =
SWITCH (
MAX ( 'Table (2)'[Indicator] ),
"C",
SUMX ( FILTER ( 'Table', [Indicator] = "A" ), [Value] )
/ SUMX ( FILTER ( 'Table', [Indicator] = "B" ), [Value] ),
"A", SUMX ( FILTER ( 'Table', [Indicator] = "A" ), [Value] ),
"B", SUMX ( FILTER ( 'Table', [Indicator] = "B" ), [Value] )
)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CaioYo ,
You could create a measure by the following formula:
First : enter a new table
Then create a measure:
Final =
SWITCH (
MAX ( 'Table (2)'[Indicator] ),
"C",
SUMX ( FILTER ( 'Table', [Indicator] = "A" ), [Value] )
/ SUMX ( FILTER ( 'Table', [Indicator] = "B" ), [Value] ),
"A", SUMX ( FILTER ( 'Table', [Indicator] = "A" ), [Value] ),
"B", SUMX ( FILTER ( 'Table', [Indicator] = "B" ), [Value] )
)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@CaioYo , Try like
C = if( max(attr([Indicador])) = "C" , divide(sum([A]),sum([B])), blank())
Final = if(max(attr([Indicador])) = "C" , [C] , sum([Value]))
Or like
C = calculate(divide(sum([A]),sum([B])), blank(), filter(attr,attr[Indicador] = "C"))
final = [C] + calculate(sum([Value]), filter(attr,attr[Indicador] <> "C"))
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |