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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DANI2021
Frequent Visitor

Power Bi Masure sum based on multiple criteria

 

 

image001.png

Hello all,

  • I have a table with various information.
    I have an order. Different material numbers are assigned to this order. In my measure, the correct number is now displayed based on the smallest criterion, i.e. the material. Now I want to have a measure that shows the sum of my current measure in the rows. The new measure should aggregate my old one based on the order.
  • https://drive.google.com/file/d/1yAOp9Rs3I7Lgbm6bOO_sKb84IjRSPM4Q/view?usp=drivesdk
2 ACCEPTED SOLUTIONS

Hi @DANI2021 ,

I updated your sample pbix file(see the attachment), please check if that is what you want. Please update the formula of the measure [Total] as below:

Total =
SUMX (
    ALLEXCEPT (
        'Fabrikleistung',
        'Fabrikleistung'[Auftrag]
    ),
    [Factory Output]
)

vyiruanmsft_0-1679966896713.png

Best Regards

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

View solution in original post

Hi,

Try this measure

Total 1 = SUMX(ALLEXCEPT('Fabrikleistung','Fabrikleistung'[Auftrag]),[Factory Output])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
v-yiruan-msft
Community Support
Community Support

Hi @DANI2021 ,

I can't access to your file when try to open your shared file link. Could you please grant the sufficient privilege to access the file? You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

vyiruanmsft_0-1679880452833.png

Best Regards

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

Sorry, should Work now

Hi @DANI2021 ,

I updated your sample pbix file(see the attachment), please find the details in the attachment. You can create a measure as below to get it:

Total = 
SUMX (
    GROUPBY (
        'Fabrikleistung',
        'Fabrikleistung'[Material],
        'Fabrikleistung'[Hauptbuch],
        'Fabrikleistung'[Auftrag]
    ),
    [Factory Output] + [Output 521000 *-1] + [Output 522000] + [Output 522002]
)

vyiruanmsft_0-1679899809611.png

Best Regards

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

This is Not quite the result i expect. I need the 315.55 in the new column at row Level. 

Hi @DANI2021 ,

Please update the formula of measure [Total] as below and check if it is your expected result... You can find the details in the attachment.

Total = 
SUMX (
    ALLEXCEPT (
        'Fabrikleistung',
        'Fabrikleistung'[Hauptbuch]
    ),
    [Factory Output]
)

vyiruanmsft_0-1679907753021.png

Best Regards

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

Thank you, 

Thats ist already going in the right direction. If i now add more Orders to my Data model, they will also bei aggegated. I want the sum of wach Order. 

Here is my extended Data model: 

 

https://drive.google.com/file/d/1Y7oJ6kHokZkXFpkS8DWzrftXZZeAjNZY/view?usp=drivesdk

 

The 1,434 ist the sum of two Orders. Gut i want to have the value for the Orders seperately. So a sum for the order 10536062 an a sum for the order 10525686.

Hi,

Try this measure

Total 1 = SUMX(ALLEXCEPT('Fabrikleistung','Fabrikleistung'[Auftrag]),[Factory Output])

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @DANI2021 ,

I updated your sample pbix file(see the attachment), please check if that is what you want. Please update the formula of the measure [Total] as below:

Total =
SUMX (
    ALLEXCEPT (
        'Fabrikleistung',
        'Fabrikleistung'[Auftrag]
    ),
    [Factory Output]
)

vyiruanmsft_0-1679966896713.png

Best Regards

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.