cancel
Showing results for
Did you mean:
New Member

## Creating a measure and mixing with another one depending on conditionals

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

C = if attr([Indicador]) = 'C' then sum([A])/sum([B]) END
Final = if attr([Indicador]) = 'C' then ([C]) else sum([Value]) end

The issues I have been facing is when I calculate a measure C and then try to calculate Final, I am not able to use if with multiple true results. When I create as a column, if does A/B at a row level, so 20/20 + 10/30 and it returns 1.3.

I really appreciate any help.
1 ACCEPTED SOLUTION
Community Support

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 Tea
m_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

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 Tea
m_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User IV

@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"))

Proud to be a Super User!

Announcements