Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
i am using in power bi single dataset .that dataset have multiple columns like below
"Master Data base"
VPRegion Plant Reduction Buckets Value
EE AR20 Supply 50
ER AR30 Scrap 45
RRR ER40 SEMS 45
GLOBAL ER50 OverStock 65
ID GL70 Materials 56
i have seperated create calculate column with below measure
NewTable = UNION ( VALUES ( 'Master Data Base'[Value]), ROW ( "Reduction Buckets", "Total FY Variation" ) )
Then made again createed new measure
Measure selc =
IF(SELECTEDVALUE(NewTable[Value])="Total FY Variation",CALCULATE(SUM('Master Data Base'[Value]),ALL(NewTable[Value])),
SUM('Master Data Base'[Value]))
but when i choose slicer like VPregion or plant . total fyi varation not showing coorect value. i have attached sample file
thanks in advance
Solved! Go to Solution.
Hi, @Anonymous
According to your description and sample pbix file, I found that you applied the visual filter on this column chart but you used the ALL() function to get the total value, I think it’s the reason for the incorrect value of the total value, I suggest you to use the ALLSelected() function to get the correct total, you can try to change the measure formula like this:
Measure selc1 =
IF(SELECTEDVALUE(NewTable[Reduction buckets])="Total FY Variation",CALCULATE(SUM('Master Data Base'[Value]),ALLSELECTED(NewTable[Reduction buckets])),
SUM('Master Data Base'[Value]))
And you can get what you want, like this:
You can download my test pbix file below
Thank you very much!
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.
Hi, @Anonymous
According to your description and sample pbix file, I found that you applied the visual filter on this column chart but you used the ALL() function to get the total value, I think it’s the reason for the incorrect value of the total value, I suggest you to use the ALLSelected() function to get the correct total, you can try to change the measure formula like this:
Measure selc1 =
IF(SELECTEDVALUE(NewTable[Reduction buckets])="Total FY Variation",CALCULATE(SUM('Master Data Base'[Value]),ALLSELECTED(NewTable[Reduction buckets])),
SUM('Master Data Base'[Value]))
And you can get what you want, like this:
You can download my test pbix file below
Thank you very much!
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.
@v-robertq-msft thank you its working . how i will make custom order wise in above measure. i tired created new table and make the realtioship between two tables not working.
1.Overstock consumption
2.New Materials
3.Scrap
4.Supply Improvements
5.Sell back to OEM
6.Target
I want to make above order is there . how i will make this ? please help
@Anonymous ,you should have additional row new table, say for Reduction Buckets, Value
that will get automatically get added to values when you use that as measure sum(Table[Value])
refer if this old soltion can help
https://community.powerbi.com/t5/Desktop/Grand-Total-in-Bar-Chart/m-p/612631
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |