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
chunkysoup
Frequent Visitor

Sum of A when B is present in different row

I apologize, I have been struggling with this mesure for the last couple of days. I am new to Dax and my expereince with Excel formulas is limited as well which is really hindering me on this problem. I need to do two calculations which end up being the reverse of each other so once I get the one down then the other should be simple.

 

We have unique Order Numbers for each project we sell. In that project may be Solid Panels or Glass Panels or Both. I want to filter out the ones that have just Glass Panels and no Solid panels and return the number of unique projects that have only Glass panels as a percentage of all projects. I then want to do the opposite and show the number of projects that have both and not just Glass Panels as a percentage of all projects. In the end I want to see the trends on products sold by region by year.

 

I believe I can use DISTINCTCOUNT for total projects based on Order Number but I can't figure out how to filter based on the values in the rows to show what I need to show

 

Any advice you are able to share would be much appreciated. Thanks in advance.

 

2019-02-19_9-28-45.jpg

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

Hello @chunkysoup 

 

download the file: https://1drv.ms/u/s!AiiWkkwHZChHj1mOwajb5i1eVvVl

 

try with the below model:

 

Capture.PNG

 

Capture.PNG

 

Only Glass Panels = 
CALCULATE(
    COUNTROWS( Orders ),
        EXCEPT(
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Glass",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            ),
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Solid",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            )
        )
)

 

Both Panels = 
CALCULATE(
    COUNTROWS( Orders ),
        INTERSECT(
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Glass",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            ),
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Solid",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            )
        )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

Hello @chunkysoup 

 

download the file: https://1drv.ms/u/s!AiiWkkwHZChHj1mOwajb5i1eVvVl

 

try with the below model:

 

Capture.PNG

 

Capture.PNG

 

Only Glass Panels = 
CALCULATE(
    COUNTROWS( Orders ),
        EXCEPT(
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Glass",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            ),
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Solid",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            )
        )
)

 

Both Panels = 
CALCULATE(
    COUNTROWS( Orders ),
        INTERSECT(
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Glass",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            ),
            CALCULATETABLE(
                VALUES( Orders[Order Number] ),
                Panels[Panel Type] = "Solid",
                CROSSFILTER( Data[Order Number], Orders[Order Number], Both )
            )
        )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

That worked! For the most part anyway. I had overly simplified it so I made a couple little changes but it works! I think the rest is me playing with the results and understanding what it is actually doing and how. Thank you so much for your help! 

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.