cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CaioYo
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.

 

ClientIndicatorValueAB
123A20  
456A10  
123B20  
456B30  
123C 2020
456C 1030

 

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:

 

IndicatorValueCFinal
A30 30
B50 50
C 0,60,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
v-yalanwu-msft
Community Support
Community Support

Hi @CaioYo  

 

You could create a measure by the following formula: 

First : enter a new table 

 v-yalanwu-msft_0-1620727640528.png

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:  

 v-yalanwu-msft_1-1620727640529.png

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.
  

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi @CaioYo  

 

You could create a measure by the following formula: 

First : enter a new table 

 v-yalanwu-msft_0-1620727640528.png

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:  

 v-yalanwu-msft_1-1620727640529.png

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.
  

View solution in original post

amitchandak
Super User IV
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors