cancel
Showing results for
Did you mean:
Highlighted
Member

## Dax grouped by measure and different table

Hi Experts,

So i have a few tables that i want to group by using Dax because i cant do it in query as there is 1 mesure column that i want to use and cant be found when im in power query.

I have Ldet(3) that i want to group by with BR and Chassis and Use the other table which is BR to sum everything by measure "Total Lab\$" that i created already .

I have this formula but its wrong

need help pls.  Anyone can help? 😞

9 REPLIES 9
Community Support Team

## Re: Dax grouped by measure and different table

Is there any relationship between Lab ,supplies and LDet? Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Member

## Re: Dax grouped by measure and different table

Hi here's the link (i will just delete it later on)

I'm using the page 2 and page 1

So my outcome (so that it wont be confusing) that i want is :
From table "LDet3" get the BR and Chassis sum them all up by the measure that i created which is "total lab\$".
Note that "total lab\$" is from another table which is "BR"

Thank u for helping.

Member

## Re: Dax grouped by measure and different table

@v-frfei-msft
And yes they all have relationship. Hence that is why i cannot understand why it wouldn't work. not sure whether if to use groupby or what but not sure about the formula. Hope you can help. I really need it 😞 Thank you!

Community Support Team

## Re: Dax grouped by measure and different table

To create the calculated column using the formula.

``````Column 3 =
CALCULATE ( SUM ( Lab[Lab\$] ), FILTER ( Lab, Lab[BR] = VALUE ( BR[branch] ) ) )
+ CALCULATE (
SUM ( Supplies[Supplies1] ),
FILTER ( Supplies, Supplies[BR] = VALUE ( BR[branch] ) )
)
``````

]

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Member

## Re: Dax grouped by measure and different table

@v-frfei-msft

mhm but i think there is some misunderstanding because the formula you gave me is different, let me explain again:
i want to sumifs the column BR and Chassis in the table "LDet3" and sum them both by "total lab\$" which is in the other table called "BR"

maybe this i will give you more understanding i have a sample in excel that i want to do in power bi:

as far as i know i cant do sumifs in power bi, is there any option to do? do let me know the formula on how to do it. thank you so much.

Member

## Group dax with measure and other table

Hi everyone,

I really need some help, on getting my numbers right.
I have a sample link in excel that i would want to achieve in power bi. In excel, its focuses on the "labour" column formula.

I've try groupby but couldn't get my numbers correctly. 😞
So in my power bi I have 2 tables that i want to include in my formula
1. LDet3 - getting the BR and Chassis
2. BR - getting the measure "Total Lab\$"

Basically, i wanted to sumifs the total lab , BR, chassis

Would be nice to have a formula on how to do it.

Thank you

Community Support Team

## Re: Dax grouped by measure and different table

Based on the data you shared, we can get the Labour column by creating the calculated column in power bi.

``````Labour2 =
CALCULATE (
SUM ( '2019L'[Lab \$] ),
FILTER (
'2019L',
'2019L'[BR] = Data2019[BR]
&& '2019L'[Chassis] = Data2019[Chassis]
)
)
``````

For more details, please check the pbix as attached.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Member

## Re: Dax grouped by measure and different table

@v-frfei-msft
Hi thank you for the reply again,

It does work, i follow your formula but the total sum from excel comparing to the Power BI total sum there is a kinda big difference of a number. Not sure if the formula is wrong or ... Sorry about it.

i've updated the excel numbers, you my take a look at this . this is till nov 14 2019 data.

thank you for the help again. 😞

Community Support Team

## Re: Dax grouped by measure and different table

In you data, the value of Data2019[BR] might not exist in  '2019L'[BR] or the value of Data2019[Chassis] does not exist in '2019L'[Chassis].

You can create a calculated column like this to have a check.

``Column = '2019L'[BR] in VALUES(Data2019[BR])``

If we get any false value, that means some of value in Data2019[BR] might not exist in '2019L'[BR].

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,706)