cancel
Showing results for
Search instead 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"))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks Top Solution Authors
Top Kudoed Authors
Users online (3,904)