Forum Discussion
Helper IHow to create Consolidation Reports
HI,
The below is the basic report with month attribute so from this report i want to create consolidation report means grouping selected months together along with the grand total (2nd pic) will show you the sample report please let me know if it is poosible in power bi desktop
Thanks.
Hi dheeraj_ami,
If your data source has related table about the group type, you could use RELATED function or lookup value function to add the group column.
If your data doesn’t exist group column, you could try to use if function or switch function to add the column. (you could refer to my earlier reply)
Sample (I want to set tom, jerry to a same group A, other to B):
Type=If ( or('Sales Record'[Custom Name]="Tom",'Sales Record'[Custom Name]="jerry "),"A","B")Type=SWITCH ('Sales Record'[Custom Name],"Tom","A","jerry","A","B")Regards,
Xiaoxin Sheng
7 Replies
KGriceMemorable Member
It's certainly possible. The easiest way is with another column that defines your groups. In this case, you're using standard calendar quarters, so that would be a good column to add. For example:
Month Quarter Revenue January 1 $ 100.00 February 1 $ 200.00 March 1 $ 300.00 April 2 $ 400.00 May 2 $ 500.00 June 2 $ 600.00 July 3 $ 700.00 August 3 $ 800.00 September 3 $ 900.00 October 4 $ 1,000.00 November 4 $ 1,100.00 December 4 $ 1,200.00 You could do this in the Get Data section, or in the Data view, or add it to your source data. If you need help figuring out that piece, please include some additional info about your current table structure and how you're pulling the data.
Once your new field is in, you could drag that onto the Values part of your table.
Hi dheeraj_ami,
Yes, it is possible to do. You could refer to KGrice’s reply.
Detail steps:
1. Add a Quarter column to the table.
Dax: Quarter Amount = ADDCOLUMNS('Month Amount',"Quarter",SWITCH('Month Amount'[Month],"Jan",1,"Feb",1,"Mar",1,"Apr",2,"May",2,"Jun",2,"Jul",3,"Aug",3,"Sep",3,"Oct",4,"Nov",4,"Dec",4))2. Create a matrix report.
3. Drag Quarter and month to rows, Amount to value.
Result:
Regards,
Xiaoxin Sheng
dheeraj_amiKGrice Anonymous Hi All,
My concept is grouping of attributes, this may be grouping of 3 or more city names Vs Revenue in row or grouping of customer names or any thing else.... it is not creating quarter from month. I need to create my owm user define report. Please let me know weather it is possible or not
customer Name Revenue
------------------ ----------
mike+john+abella 35000
jim 1500
jhonny+amy+sony 20500
OR
CityName Revenue
------------ -----------
New York+Dellas+Denver 525500
Texas+miami 2500
Washington 6500
Thanks