Percentage between 2 columns - DAX Synthax

[Anonymous]

Hi the community, 

I'm starting in Power BI and generaly in data science, and for this reason i have a problem which look very simple but i'm breaking my head agains my office since too much time to solve it. 

Please find below my data : 

Vendor name	Condition	Order Quantity
Aero Inc	Scrap	9000
Aero Inc	Overhaul	1000
Jack Aero	Scrap	5000
Jack Aero	Overhaul	1000
Mr. Jacket enterprise	Scrap	1500
Mr. Jacket enterprise	Overhaul	25000

Etc... 

I would like to find a formula for calculate the percentage of scrap part and overhaul part by vendor compare to the total quantity order. 

Or, maybe i need to do a new column ? 

Honnestly, i know it's look simple... But i didn't have the logic. 

Thanks for your help ! 

[vivran22]

Hello @Anonymous 

Welcome to the Power BI community.

You can achieve this by creating new measures:

Total Order = SUM(dtTable[Order Quantity])

%Scrap = 
VAR SCRAP =
    CALCULATE ( [Total Order], FILTER ( dtTable, dtTable[Condition] = "Scrap" ) )
RETURN
    DIVIDE ( SCRAP, [Total Order] )

%Overhaul = 
VAR OVERHAUL =
    CALCULATE ( [Total Order], FILTER ( dtTable, dtTable[Condition] = "Overhaul" ) )
RETURN
    DIVIDE ( OVERHAUL, [Total Order] )

It will give you following results:

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

Connect on LinkedIn

[Anonymous]

I do a new measure separate for the total order. 

I did'nt understand why i have one in Value with your formula ? 

Thanks again for your help... 

[Anonymous]

@vivran22,

There's a golden rule of data modelling in Power BI: Never filter a table when you can filter a column. There are very good reasons behind this (also related to performance) but if you want to know more about it, you'll need to find the relevant articles on www.sqlbi.com, where the ultimate knowledge about DAX resides.

As for the original question itself... The measures should be written this way:

[Total Order] = SUM(dtTable[Order Quantity])

%Scrap = 
VAR __scrap =
    CALCULATE (
       [Total Order],
       KEEPFILTERS(dtTable[Condition] = "Scrap")
    )
var __total = [Total Order]
RETURN
    DIVIDE ( __scrap, __total )

%Overhaul = 
VAR __overhaul =
    CALCULATE ( 
        [Total Order],
        KEEPFILTERS( dtTable[Condition] = "Overhaul" )
    )
var __total = [Total Order]
RETURN
    DIVIDE ( __overhaul, __total )

Best

darlove

[Anonymous]

Darlove, 

I have the same problem. I get one in values with this formula. Where i did wrong ? 

I apologize if its obvious... thanks for your help. 

Best 

[vivran22]

@Anonymous 

I believe it is the formatting issue. Select the measure(%Scrapping) > Go to Measure Tools > And select the % formatting. It should resolve this.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

No it did'nt still work. 

I try to changed the formula like this : 

1. %Overhaul =
   VAR __overhaul = CALCULATE([Total Order], Sheet1[Condition ] = "overhaul")
   var __total = [Total Order]
   RETURN
   DIVIDE(__overhaul, __total)

It's worked for the the total overhaul and scrap. 

But i can't have the scrap ration by example, for each vendor compare to the total. In fact, the result like you see doesnt make any sense for each one. 

I tried to add a new measure like that : 

1. Just Scrap =
   VAR __scrap = CALCULATE([Total Order], KEEPFILTERS(Sheet1[Condition ] = "scrap"))
   VAR __total = [Total Order]
   RETURN
   DIVIDE(__scrap, __total)
   
   But i had again 1 in value... 
   
   Could you please advise me ? 
   
   Your coments are very helpful, thank you so much. 

[Anonymous · Accepted solution]

Or you might want this variation of the measure:

[% Scrap] =
VAR __scrap =
	CALCULATE(
		[Total Order],
		KEEPFILTERS(Sheet1[Condition] = "scrap")
	)
VAR __total =
	CALCULATE(
		[Total Order],
		ALL( Sheet1[Condition] )
	)
RETURN
	DIVIDE(__scrap, __total)

Best

D

[Anonymous]

Awesome ! 

Thank you a lot Dar for you clearly explanations. Help me a lot for the future and my understanding of DAX synthax ! 

Wish you the best  ! 

🙂 

[Anonymous]

This should work:

[% Scrap] =
VAR __scrap =
	CALCULATE(
		[Total Order],
		KEEPFILTERS(Sheet1[Condition] = "scrap")
	)
VAR __total = [Total Order]
RETURN
	DIVIDE(__scrap, __total)

I must work. Bear in mind though, that when you filter by Overhaul, then this will return 0 out of necessity and if you filter by Scrap, it'll return 1 (100%). This is correct. If you don't select from the Condition, then you'll get the % of scrap in the selection. Change 'scrap' to 'overhaul' and you'll get the same behavior for Overhaul.

Best

D

[Anonymous]

Create measure
Total scrap quantity= calculAte(sum(table[quantity]),filter(table,table[condition]="scrap")

Similarly calculate for overhaul.

If you want to compare it with total use below dax measure
Total= sum(table[quantity])

Thanks
Pravin

If it resolves your problem mark it as a solution and give Kudos.