Percent of grand total of distinct values in data with row duplication

[DK_analyst]

Good morning, Power BI gurus. I humbly present the problem that has stumped me for the last 24 hours of screen time (I wish I were kidding):

I am working with sales data that has a significant amount of duplication in it. The duplication was done deliberately, as it was neccessary for another calculation I had to make.

The tasks are to find both the sum of the distinct product's sale priceover a given time period, and the percent of the overall grand total each distinct product's sale price is. The data also has a date component to it, as the final report will need to be sliced by time period.

Here is some example data:

Year	Product	Sale Price
2022	A	100
2022	A	100
2022	A	100
2022	A	100
2022	A	100
2022	B	150
2022	B	150
2022	C	50
2022	C	50
2022	C	50
2022	C	50
2022	D	200
2022	D	200
2022	D	200
2022	D	200
2022	D	200

The final report with the above aggregations is supposed to look like this:

Year	Product	Sale Price	Percent
2022	A	100	20%
2022	B	150	30%
2022	C	50	10%
2022	D	200	40%
	Total	500	100%

I was able to create a measure that accurately calculates the sale price and the distinct sum correctly using SUMX and Calculate (I'm still learning about context transitions, and I know that's what's happening here):

SUMX(
     VALUES('Data'[Product]),
     CALCULATE(
          MAX('Data'[Sale Price]),
     )
)

However, I am unable to figure out how to accurately calculate the percent column. Intuitively it's simple: just take the sale price of each displayed value and divide it by the displayed grand total. However I cannot figure it out.

Any help or guidance here would be much appreciated.

[Padycosmos · Accepted solution]

This video covers your requirement :

https://www.youtube.com/watch?v=3IHsIngGdjU

[DK_analyst]

@Greg_Deckler Thank you very much for your reply. I tested the solution out, but unfortionatly that it didn't work. Your code returns 100% for each product, and after I debugging I discovered that the "__Total" variable is actually recalculating the exact value my original code did for the sale price, and 100/100 will always be %100.

Any other ideas? 😅

[Greg_Deckler]

@DK_analyst Here is one way:

Price Percent = 
    VAR __Row = AVERAGE(Data[Sale Price])
    VAR __Table = SUMMARIZE(ALL('Data'),[Year],[Product],"__Avg",AVERAGE(Data[Sale Price]))
    VAR __Total = SUMX(__Table,[__Avg])
    VAR __Result = 
        IF(
            ISINSCOPE('Data'[Product]),
            DIVIDE(__Row, __Total),
            DIVIDE(__Total, __Total)
        )
RETURN
    __Result