Creating a measure and mixing with another one depending on conditionals

[CaioYo]

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.

[v-yalanwu-msft · Accepted 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.  

[amitchandak]

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