topic Which Power BI Dax function should I use to calculate? in DAX Commands and Tips

Which Power BI Dax function should I use to calculate?

sumin — Wed, 11 Dec 2024 05:22:39 GMT

Like Calculate, I would like to express the calculation for Target as follows.

Subcategories such as A-1 and B-1 are Quantity / Hours, and upper classifications such as A and B are calculated as target sum of the subcategories.
How do I write a Dax function?

Re: Which Power BI Dax function should I use to calculate?

FreemanZ — Wed, 11 Dec 2024 06:07:22 GMT

hi sumin ,

DAX could be very tricky handling hierrachies.

In best case, it can be as easy as :

measure = DIVIDE ( SUM(data[quantity]), SUM(data[Hours]))

Re: Which Power BI Dax function should I use to calculate?

sumin — Wed, 11 Dec 2024 07:46:10 GMT

Thank you for the answer, but it's not the result I want.

Subcategories is possible in that way, but I want to express the upper categories as the sum of the values of the subcategories.

Re: Which Power BI Dax function should I use to calculate?

Anonymous — Thu, 12 Dec 2024 11:08:21 GMT

Hi sumin,

Thanks for reaching out Microsoft Fabric community Forum

For calculating Target =Quantity/Hours create a calculated column use the below DAX Formula

Subcategory Target =

IF(

CONTAINSSTRING('Sheet2'[Name], "-"),

'Sheet2'[Quantity] / 'Sheet2'[Hours],

BLANK()

)

For calculating Sum of A Target Use below DAX measure

Sum of A Target =

CALCULATE (

SUM ( 'Sheet2'[Subcategory Target]),

FILTER (

'Sheet2',

'Sheet2'[Name] = "A-1" || 'Sheet2'[Name] = "A-2" || 'Sheet2'[Name] = "A-3"

)

For calculating Sum of B Target Use below DAX measure

Sum of B Target =

CALCULATE (

SUM ( 'Sheet2'[Target] ),

FILTER (

'Sheet2',

'Sheet2'[Name] = "B-1" || 'Sheet2'[Name] = "B-2"

)

I implemented the solution using the given sample provided by you with the above DAX formula. For better understanding, I am attaching an image below.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you

Re: Which Power BI Dax function should I use to calculate?

vivek31 — Thu, 12 Dec 2024 10:41:31 GMT

Hi sumin,

first you shoud create a sub-category column.

and create measure.

Sum traget = IF(ISINSCOPE('Table (5)'[sub-category]),SUM('Table (5)'[target]))

Re: Which Power BI Dax function should I use to calculate?

Anonymous — Mon, 23 Dec 2024 17:24:16 GMT

Hi sumin
Thank you for your patience. From the steps outlined below, please let us know if anything was helpful to you, so that we can convert it into a formal answer. If so, we would appreciate it if you could Accept it as a solution and drop a 'Kudos' so other members can find it more easily.
Thank you.

Re: Which Power BI Dax function should I use to calculate?

Anonymous — Tue, 24 Dec 2024 04:49:55 GMT

Hi sumin
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Re: Which Power BI Dax function should I use to calculate?

Anonymous — Sat, 28 Dec 2024 14:50:11 GMT

Hi sumin
We haven’t heard back since our last response and wanted to check if your query has been resolved. If not, please feel free to reach out for further assistance. If it has been resolved, kindly mark the helpful reply as the solution to make it easier for others to find. A kudos would also be greatly appreciated!
Thank you.