cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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 .

 
 

42.JPG40.JPG

I have this formula but its wrong 

 

 41.JPG


need help pls.  Anyone can help? 😞

 
9 REPLIES 9
Community Support Team
Community Support Team

Re: Dax grouped by measure and different table

Hi @Ellensantiago1 ,

 

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.

Re: Dax grouped by measure and different table

@v-frfei-msft 

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.

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
Community Support Team

Re: Dax grouped by measure and different table

Hi @Ellensantiago1 ,

 

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] ) )
    )

 Capture.PNG

]

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

Re: Dax grouped by measure and different table

@v-frfei-msft 
Hi thanks for the reply,

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:
43.JPG
and the link too : 

https://drive.google.com/file/d/1_l1P-9ykA_UQcf6gxsUz7l8ndj4gvUXT/view?usp=sharing


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. 

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.
https://drive.google.com/file/d/1_l1P-9ykA_UQcf6gxsUz7l8ndj4gvUXT/view?usp=sharing

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 

45.JPG46.JPG

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

Thank you

Community Support Team
Community Support Team

Re: Dax grouped by measure and different table

Hi @Ellensantiago1 ,

 

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]
    )
)

Capture.PNG

 

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.

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. 
47.JPG48.JPG 

 

i've updated the excel numbers, you my take a look at this . this is till nov 14 2019 data. 
https://drive.google.com/file/d/14jDDZY5cIb2vgsHyidPKbzy5P_LoLnuE/view?usp=sharing



49.JPG

thank you for the help again. 😞

Community Support Team
Community Support Team

Re: Dax grouped by measure and different table

Hi @Ellensantiago1 ,

 

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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