Put slicer selection total to each row

[dokat]

Hi,

I have below formula that i use to calculate share. The issue i am having is how can i put slicer selection total as static value to each row like in below example (Total Sales column). Appreciate any help!

Region Fixed State Sales = CALCULATE(Calculate(SUM('Region'[Values]),'POS CY'[Slicer] IN {"L52W"},ALL(Region[Category],Region[Brand],Region[Manufacturer])))

For Ex: 

	Sales	Total Sales	Share
North	10	360	3%
South	20	360	6%
East	30	360	8%
West	40	360	11%
Notheast	50	360	14%
Northwest	60	360	17%
Southeast	70	360	19%
Southwest	80	360	22%
Total	360

[jdbuchanan71 · Accepted solution]

@dokat 

Is this what you are looking for?

Region Category Fixed = CALCULATE([Region Category Selected], ALLSELECTED(Region[Census Division]))

[parry2k · Accepted solution]

@dokat if yes then this is your measure

Region Category Fixed = CALCULATE([Region Category Selected],ALLSELECTED ( Region) ) 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k]

@dokat  Thanks for the feedback. If you haven't already, do subscribe to my YT channel. I'm posting almost a video every day since Jan 1st and most of these videos are inspired by real-time problems and/or questions posted on the Power BI Community forum. Cheers!!

[parry2k]

@dokat I have a video on this function on my YT channel, check it out here. https://youtu.be/XXVRwvni4Ms

[dokat]

@parry2k Video was very helpful and educational.

[dokat]

@parry2k I will check it out. 

[parry2k · Accepted solution]

@dokat this should fix it:

Region Category Selected = 
CALCULATE(
    SUM ( Region[Values] ),
    'POS CY'[Slicer] IN {"L52W"},
    CROSSFILTER (
        POS[unique],
        Bridge[Unique],
        BOTH
    )
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[dokat]

Please disregard my quetsion from last night as changing (Region) to Map fixed my issue. Thanks for all your help

[dokat]

@parry2k Thank you! It worked. I never used crossfilter function before i need to study.

[jdbuchanan71]

@dokat 

@parry2k is correct, my measure would not work if you put any other field from the Region table on the rows.  You should do it like this instead.

Region Category Fixed = CALCULATE([Region Category Selected], ALLSELECTED(Region))

[dokat]

@jdbuchanan71 @parry2k I updated the formula with ALLSELECTED(Region). Thanks!

[parry2k]

@dokat @jdbuchanan71 measure will work but if you use any other column in the visualization from the Region table, it will not work. Good luck!

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[dokat]

@jdbuchanan71 @parry2k I have one more question related to this issue and the file i shared.

When select all is selected in category slicer 'region Category Selected" returns $983,389,587.89 however it should return $2,535,250,045.49. What may cause this issue? Appreciate any help.

[parry2k · Accepted solution]

@dokat if yes then this is your measure

Region Category Fixed = CALCULATE([Region Category Selected],ALLSELECTED ( Region) ) 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[jdbuchanan71 · Accepted solution]

@dokat 

Is this what you are looking for?

Region Category Fixed = CALCULATE([Region Category Selected], ALLSELECTED(Region[Census Division]))

[dokat]

@jdbuchanan71 Thank you, this is exactly what i was looking for.

[parry2k]

@dokat is this what you are looking for?

[dokat]

@parry2k Yes i do. @jdbuchanan71 solution worked. Thank you boh for looking into this

[parry2k]

@dokat anytime you ask a question, you have to share your data model, how tables are connected and from which table you are using what columns? Simply sharing the screen shot will not help to answer the question. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[dokat]

@parry2k I uploaded the sample file to dropbox. Please see below link, hope this helps.

https://www.dropbox.com/s/6fuhafrvn5j4fau/Test%20Data.pbix?dl=0

[parry2k]

@dokat did you test what I provided? What is the result you are getting?

[dokat]

@parry2k Yes i did test your formula. Logic makes sense however its returning wrong values. It makes me wonder whether it is due to a link table.

Please see below screenshot. I'd like each row under "Region Category Fixed" column to show $55,108,306.86. all() function result total of the entire column. I'd like total of only selected value in slicer. Hope this clarifies. Is there anyway to do this?