Percent of Total

[Dippnsidways]

Hi All,

I am not sure have I am doing wrong and would like some guidance. 

I would like the calculate the percent of theCode as a total.

Here is my data set:

I want to create another column (table below) where it takes theCode quantity, divides it with theTotal quantity of all codes for that day, and that column value is a percent. 

I hope this makes sense.

When I try to type a formula it returns infiniti for codes.

Maybe I'm making a small typo

Thanks!

year	Month	Day	theCode	quantity	% per Code
2020	January	21	0	8853	= 8853 / (8853 + 300 + 187 + 58)
2020	January	21	131	300	= 300 / (8853 + 300 + 187 + 58)
2020	January	21	133	187	= 187 / (8853 + 300 + 187 + 58)
2020	January	21	51	58	= 58 / (8853 + 300 + 187 + 58)
2020	January	22	0	8921	= 8921 / (8921 + 490 + 277 + 92 + 2)
2020	January	22	131	490	= 490 / (8921 + 490 + 277 + 92 + 2)
2020	January	22	133	277	= 277 / (8921 + 490 + 277 + 92 + 2)
2020	January	22	51	92	= 92 / (8921 + 490 + 277 + 92 + 2)
2020	January	22	77	2	= 2 / (8921 + 490 + 277 + 92 + 2)
2020	January	23	0	8773	= 8773 / (8773 + 810 + 338 + 3 + 283)
2020	January	23	131	810	= 810 / (8773 + 810 + 338 + 3 + 283)
2020	January	23	133	338	= 338 / (8773 + 810 + 338 + 3 + 283)
2020	January	23	2	3	= 3 / (8773 + 810 + 338 + 3 + 283)
2020	January	23	51	283	= 283 / (8773 + 810 + 338 + 3 + 283)

[v-alq-msft · Accepted solution]

Hi, @Dippnsidways 

You may create a measure as follows.

% per Code = 
DIVIDE(
    SUM('Table'[quantity]),
    CALCULATE(
        SUM('Table'[quantity]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Date] = MAX('Table'[Date])
        )
    )
)

Result:

To format the measure, you may click the 'Modeling' ribbon and then the '%' icon.

Best Regards

Allan

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

[amitchandak]

Assuming you have date in your table and that date is joined with a calendar date dimension. Please try following

% day = divide(SUM(Table[Quantity]),CALCULATE(SUM(Table[Quantity]),ALLEXCEPT(Date,Date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Dippnsidways]

amitchandak,

Thanks for your suggestion.

Sorry for not including the name of my table.

I've tried your suggestion but did not yield the result I was looking for.

I am to guess I did not have have a date calendar and marked as the date in model view.

I will follow the links you have provided and build on that.

Thanks!