Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

@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!

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

@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. 

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

@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. 😞

Anonymous
Not applicable

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

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors