cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shri0025
Helper II
Helper II

How to create table for first 3 month then Qtr 1, second 3 month Qtr 2 for

Hi Hello,

I have category and date. i need to create month wise sales like

    

         :-   Apr, May,June Then Qtr 1, July, Aug, Sep, Than Qtr 2

         Jan,Feb,March month then Qtr 4

 Visul show in Stacked  Column or Clustered  Please help if possible.

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @shri0025 ,

 

If you want to sort strictly by "month-quarter- month-quarter", you can try the following.
1. Create a new table and add Index column.

 Table.FromRecords({
        [Name = "Apr-19", MonthNum = 4],
        [Name = "May-19", MonthNum = 5],
        [Name = "Jun-19", MonthNum = 6],
        [Name = "Q1", MonthNum = null],
        [Name = "Jul-19", MonthNum = 7],
        [Name = "Aug-19", MonthNum = 8],
        [Name = "Sep-19", MonthNum = 9],
        [Name = "Q2", MonthNum = null],
        [Name = "Oct-19", MonthNum = 10],
        [Name = "Nov-19", MonthNum = 11],
        [Name = "Dec-19", MonthNum = 12],
        [Name = "Q3", MonthNum = null],
        [Name = "Jan-19", MonthNum = 1],
        [Name = "Feb-19", MonthNum = 2],
        [Name = "Mar-19", MonthNum = 3],
        [Name = "Q4", MonthNum = null]
    })

vkkfmsft_0-1639982166106.png       vkkfmsft_1-1639982189546.png

 

2. Create the following measures.

sales = 
CALCULATE( 
    SUM('Table'[Total Sales]),
    FILTER(
        'Table',
        'Table'[Month Name] = MAX(AxisTable[Name]) 
    )
)
Measure = 
var QNo = VALUE( RIGHT( MAX(AxisTable[Name]), 1 ) )
var Q_M = If( QNo * 3 + 1 < 12, QNo * 3 + 1, 1 )
var Q_Sales = 
    CALCULATE( 
        SUMX(
            ALLSELECTED(AxisTable[Name]),
            [sales]
        ),
        FILTER(
            ALL(AxisTable),
            AxisTable[MonthNum] in { Q_M, Q_M+1, Q_M+2 }
        )
    )
return 
    IF(
        left( MAX(AxisTable[Name]), 1 ) = "Q",
        Q_Sales,
        [sales]
    )

 

3. Sort the Name column by Index column and sort the bar chart by Name column in ascending order.

 

vkkfmsft_2-1639982852489.pngvkkfmsft_3-1639982897722.png

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

View solution in original post

13 REPLIES 13
v-kkf-msft
Community Support
Community Support

Hi @shri0025 ,

 

If you want to sort strictly by "month-quarter- month-quarter", you can try the following.
1. Create a new table and add Index column.

 Table.FromRecords({
        [Name = "Apr-19", MonthNum = 4],
        [Name = "May-19", MonthNum = 5],
        [Name = "Jun-19", MonthNum = 6],
        [Name = "Q1", MonthNum = null],
        [Name = "Jul-19", MonthNum = 7],
        [Name = "Aug-19", MonthNum = 8],
        [Name = "Sep-19", MonthNum = 9],
        [Name = "Q2", MonthNum = null],
        [Name = "Oct-19", MonthNum = 10],
        [Name = "Nov-19", MonthNum = 11],
        [Name = "Dec-19", MonthNum = 12],
        [Name = "Q3", MonthNum = null],
        [Name = "Jan-19", MonthNum = 1],
        [Name = "Feb-19", MonthNum = 2],
        [Name = "Mar-19", MonthNum = 3],
        [Name = "Q4", MonthNum = null]
    })

vkkfmsft_0-1639982166106.png       vkkfmsft_1-1639982189546.png

 

2. Create the following measures.

sales = 
CALCULATE( 
    SUM('Table'[Total Sales]),
    FILTER(
        'Table',
        'Table'[Month Name] = MAX(AxisTable[Name]) 
    )
)
Measure = 
var QNo = VALUE( RIGHT( MAX(AxisTable[Name]), 1 ) )
var Q_M = If( QNo * 3 + 1 < 12, QNo * 3 + 1, 1 )
var Q_Sales = 
    CALCULATE( 
        SUMX(
            ALLSELECTED(AxisTable[Name]),
            [sales]
        ),
        FILTER(
            ALL(AxisTable),
            AxisTable[MonthNum] in { Q_M, Q_M+1, Q_M+2 }
        )
    )
return 
    IF(
        left( MAX(AxisTable[Name]), 1 ) = "Q",
        Q_Sales,
        [sales]
    )

 

3. Sort the Name column by Index column and sort the bar chart by Name column in ascending order.

 

vkkfmsft_2-1639982852489.pngvkkfmsft_3-1639982897722.png

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

jppv20
Solution Sage
Solution Sage

@shri0025 

So you want to show in one visual the data for each month as well as the total for each quarter?

Yes

Can you share an example of your input data please?

Hi Please find input data show to Visual bar Chart

 

 

 

 

Month NameTotal Sales
Apr-19263417035
May-19317719685
Jun-19329506493
Q1910643213
Jul-19286064978
Aug-19350989685
Sep-19417868113
Q2 1054922777

@shri0025 

Are you sure this is what your data looks like when it is imported into PowerBi?

In that case you can go to PowerQuery and add an index column:

jppv20_1-1639564208539.png

Then go back to the report and select the column Month Name --> Sort by column: Index

Put Month Name and Total Sales in the Column chart and sort by Month:

jppv20_0-1639564421857.png

 

If I answered your question, please mark it as a solution to help other members find it more quickly.

Hi Thanks For Help 

 

I had mentioned Q1,Q2 only for reference. Otherwise My data only has month wise sales.

 

I want to add QTR bar as well after 3 months in the BAR CHART.

 

Please suggest.

@shri0025 In that case, can you please share the data how you import it in PowerBi?

Is it like this?

Month Name

Total Sales

Apr-19

263417035

May-19

317719685

Jun-19

329506493

Jul-19

286064978

Aug-19

350989685

Sep-19

417868113

 

Hi 

Yes Look like the Same 

@shri0025 

I'm not sure if this is the easiest solution, but it worked for me:

Go to the Query Editor and add a column for the Qtr:

jppv20_0-1639574587749.png

Duplicate the table twice, in one table you keep only the Month Name column and in the other the Qtr Column:

jppv20_1-1639574679282.png 

jppv20_2-1639574692486.png

Remove the duplicates in the Qtr column and change the name to Month Name

Then append the last table to the second table and add an index column:

jppv20_3-1639574764315.png

Close & apply and go to relationships. Create two relationships between the tables:
Table - Month Name to Table (2) - Month Name and Table - Qtr to Table (2) - Month Name  (inactive).

jppv20_4-1639574931393.png

 

Then create this measure:

Measure =
var Sales = SUM('Table'[Total Sales])
return
IF(ISBLANK(Sales),CALCULATE(SUM('Table'[Total Sales]),USERELATIONSHIP('Table'[Qtr],'Table (2)'[Month Name])),Sales)
 
Put the measure and Month Name column from table 2 in the visual to get this result:
jppv20_5-1639575001742.png

 

Hope this helps!

 

If I answered your question, please mark it as a solution to help other members find it more quickly.

 

 

 

 

Capture.JPG

jppv20
Solution Sage
Solution Sage

Hi @shri0025 ,

 

What does your input data look like?

And what is the expected result?

IMG_8307.jpg

 

Look Like Bellow 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors
Users online (289)