Percentage of filtered total with slicer

[Anonymous]

Hi all, I have been struggling with this percentage calculation problem.

My table called Timesheet has four columns: "date", "Level", "A", "Duration" . Level and A are hierarchy, Level contains A. I wanted to show the percentage of total in a given time window(the date slicer).

I made a measure called percentage using: 

Percentage = DIVIDE(Timesheet[Duration],CALCULATE(SUM(Timesheet[Duration]),ALLSELECTED(Timesheet)))

It works fine when I am not using the date slicer. The percentage in A adds up to the percentage of the selected Level.

However, if I use the slider, meaning I am filtering the date column, the percentage in A adds up to the percentage of the grand total, of the whole dataset. 

I want the percentage in A to add up to 41.52% in this case, the percentage of the selected level of the filtered total.

Please help!!! I tried soooo many methods, none of them worked.

[Syndicate_Admin]

I found perfect what you indicated step by step for a table.

How can I make it possible for me to have the same thing in sight in the same way with a card?

It happens to me that when I want to select something I need that also in the card reflects the percentage of the accumulated of that period.

 

For example I selected the first row and another object in my view but the card is still at 100%.

What kind of formulation can I use for a card to follow that dynamism hooked to display and date ranges.

[PaulDBrown]

How is you rmodel set up? Can you share a sample PBIX file?

[PaulDBrown]

@Anonymous 

Try:

1) Sum Duration = SUM(Timesheet[Duration])

2) Selected rows = CALCULATE([Sum Duration], ALLSELECTED(Timesheet)

3) Percentage = DIVIDE([Sum Duration], [Selected Rows])

Here is  the equivalent using a dummy dataset:

Using:

Sum of Sales = SUM('Sales'[Actuals])

Selected rows = CALCULATE([Sum of Sales], ALLSELECTED('Sales'))

Percentage over total = DIVIDE([Sum of Sales], [Selected rows])

(Just note that in my dummy model I'm using a Calendar table for the date slicer, though I don't think it should make a difference, but...)

[Anonymous]

It shows as the same as the grand total column in my post. Is it possible because I didn't make a Hierarchy? Level and A are hierarchical but I need to separate them into two visuals since I have four levels in total and I simplified the data.

[PaulDBrown]

@Anonymous 

Is the example with my dummy data what you are expecting?

I'm not sure what you mean by the hierarchies. In my example Item is a child to Channel (so channel contains item values)

[Anonymous]

Your data is slightly different. I uploaded my revised data. Feel free to play around.

https://drive.google.com/file/d/1DhijmuHeGKpynmGONq58P4dhwlvVGAQ_/view?usp=sharing

[PaulDBrown]

@Anonymous 

See if any of these fulfill your needs: The top matrix has both level of hierarchies; the bottom matrix only level B:

If not, which is the number you need? (you could do a mockup in Excel)

I'm attaching the PBIX file

[Anonymous]

Thanks!

The "percentage sel rows" without level A filter is what I want. 

When I select one of the levels in level A, is it possible to have the percentage add up to the percentage of filtered-date total of that level? 50.23% in this case.

[PaulDBrown]

@Anonymous 

Ok, see if this is what you need:

(Beware I've added dimension tables and used them in visuals and measures)

[Anonymous]

It is very close! Sorry I might not be clear enough. Tried to figure it out by myself on your file, still don't know how to. Please see my table based on the screenshot scenario.

[v-robertq-msft]

Hi, @Anonymous 

Can PaulDBrown’s output help you to meet your requirement now?

If so, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[PaulDBrown]

@Anonymous 

How about now?

Revised file attached

[Anonymous]

Amazing! Looks exactly like what I want. Just I don't think you attached the file. Can you please? Thanks!

[PaulDBrown · Accepted solution]

@Anonymous 

Apologies for not attaching the file. See if this one works

[brinky]

Thanks for sharing @PaulDBrown, what is the diffence between the "Pecentage Sel Row" and the "IFINSCOPE" measures?

Thanks in advance.

[themistoklis]

@Anonymous 

Can you try the following formula and see if it works?

Percentage = DIVIDE(Timesheet[Duration], SUMX(VALUES( Timesheet[A] ), CALCULATE(SUM(Timesheet[Duration]),ALLSELECTED(Timesheet))))

OR this formula

% Share = 
VAR Volume =
    SUM(Timesheet[Duration]
VAR AllVolume =
    CALCULATE ( SUM(Timesheet[Duration],ALL('Timesheet'[A] ) )

RETURN
    DIVIDE ( Volume, AllVolume )

[A] is the field on the left hand side of the table

[Anonymous]

Thanks first!

First formula: it's getting worse.

Second formula: %share adds up tp 100%. It is the same as % grand total by default.

[themistoklis]

@Anonymous 

Could you share the file with us? Please hide any sensitive data

[Anonymous]

Please see this, I made a similar dashboard, the data structure is the same, the column names are slightly different but more clear.

https://drive.google.com/file/d/1DhijmuHeGKpynmGONq58P4dhwlvVGAQ_/view?usp=sharing

[themistoklis]

@Anonymous 

Try this formula.

Seems to be working.

Percentage = DIVIDE(SUM(Worksheet[Duration]),SUMX(VALUES( Worksheet[Level A] ),CALCULATE(SUM(Worksheet[Duration]),ALL(Worksheet))))