Create a measure percentage of total

[jdballard30]

I'm trying to crate a measure that calculates a percentage of the total that I can't get working. Below in red is what I'm trying to calculate.

Some more details: I have two dates, received date and transfer date. I created a calculated column that shows "Pending" if the received date is populated, and the transfer date isn't, Otherwise, it shows "<Month> <Year>" (the transaction is considered "Pending" if the money isn't actually transferred.)  I need that, because I need a row for pending in my table.

[v-alq-msft · Accepted solution]

Hi, @jdballard30 

Based on your description, I create data to reproduce your scenario.

I have something to need to be confirmed. I wonder 'Actual Amount' is a column or a measure. If it is a measure, you need to create another measure to achieve the result. If it is a column, you may operate as follows.

First, you may drag the column 'Actual Amount' in the 'Value' area again, right-click it, select 'Show value as', choose 'Percentage of column total'.

Result:

If I misunderstand your thought, please show me your sample data and expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.

Best Regards

Allan

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

[jdballard30]

@v-alq-msft, I haven't tried your solution, or @amitchandak's yet. I thought I'd included an example pbix in my post, but I didn't include the link. Here's a file with fake data. And to answer your question, actual amount is a column, and not a measure.

[v-alq-msft]

Hi, @jdballard30 

Thanks for providing your sample data. If 'Actual Amount' is a column, you may operate as what I said in my last reply. 

Here is the result with your sample data.

Best Regards

Allan

[jdballard30]

Thank you, @v-alq-msft, that worked. Additional question: is there any way to format that so that it doesn't have decimal places in the percentage, so it will show 65% instead of 65.23%? It didn't create a measure for that field, so I can't see how it's calculating that field or format it.

[v-alq-msft]

Hi, @jdballard30 

You may also create a measure to achieve your requiremen as follows.

Actual Amt % = 
DIVIDE(
    SUM(Transactions[Actual Amount]),
    CALCULATE(
            SUM(Transactions[Actual Amount]),
            ALLEXCEPT(Transactions,Transactions[Received Date].[Year],'Transactions'[Received Date].[Month])
    )
)

Then you can make the measure selected and click '%' in the 'Modeling' ribbon.

Result:

[amitchandak]

It is possible to join the same date dim with two dates and use as per need. But this is the case of two date dimensions.

Try this .

Create a month -year column in both columns. Also the sort column. For transfer date have pending at null.

Month - year = format(date,"MMM-YYYY")

Month Year Sort = format(date,"YYYYMM")

Have these for both dates. and now use these as row and column.

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