Sum of A when B is present in different row

[chunkysoup]

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.

[LivioLanzo · Accepted solution]

Hello @chunkysoup 

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

try with the below model:

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

[chunkysoup]

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!