Quarter Wise Calculations in a Table format

[E5254730]

Hello,

I have a below table format in Excel and would like to have similar output in PBI report

My Excel data is as below:

I would only like to have the calculations from Project Type column (A) were it only says "Strategic"

For instance below is how the calculations is done for "Chronological" row for Q1

Project Type = Strategic

Classification column = Chronological

Status = Inactive

Reporting Date = Jan, Feb and Mar

So the output will be : Count 67, Sales 4820408

And if Selected Reporting Dates are Apr, May and June, below will be the output for Inactive Status:

Count 38, Sales 1479441

And lastly I need the below format in cards:

Please advise how can we achieve this

[Deku · Accepted solution]

Add a date table, joined to the reporting date column, with a quarter column

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Add classification to rows and quarter to columns for a matrix visual. Then you would need measures for the values i.e.

Active = calculate( countrows( facts), fact[status] = "Active" )

Inactive = calculate( countrows( facts), fact[status] = "Inactive" )

Sales = sum( fact[sales] )

Not sure how you want to calculate the card percentages or the green Vs grey sales

[FBergamaschi · Accepted solution]

OK this is now clear

Total Sales/Total Sales = Total Sales of Q1 / Total Sales of Q2

what about Active, what is the meaning of that? So I can check the right DAX cose of that

@Deku suggested

Active = calculate( countrows( facts), fact[status] = "Active" )

Inactive = calculate( countrows( facts), fact[status] = "Inactive" )

Sales = sum( fact[sales] )

Rest will be

Total Sales Q1 = CALCULATE ( [Sales], Calendar[Quarter]="Q1" )

Total Sales Q2 = CALCULATE ( [Sales], Calendar[Quarter]="Q2" )

Ratio = DIVIDE ( [Total Sales Q1], [Total Sales Q2] )

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

but I want to be sure that Active is a count of rows or might be a number of distinc values of some column, can you clarify this?

Thanks

[E5254730]

@Deku @FBergamaschi @v-sdhruv 

Thank you! everyone.

I was able to achieve the desired output with your provided inputs.

Thanks once again.

[v-sdhruv]

Hi @E5254730 ,

Just wanted to check if you got a chance to review the suggestions provided and whether that has helped to resolve your issue? If you are still facing the error, I would encourage you to post your error details so that we can assist you better.

Thank you

[v-sdhruv]

Hi @E5254730 ,

Just wanted to check if you got a chance to review the suggestions provided and whether that has helped to resolve your issue? If you are still facing the error, I would encourage you to post your error details so that we can assist you better.

Thank you

[E5254730]

@FBergamaschi 

Total Sales/Total Sales = Total Sales of Q1 / Total Sales of Q2

[FBergamaschi · Accepted solution]

OK this is now clear

Total Sales/Total Sales = Total Sales of Q1 / Total Sales of Q2

what about Active, what is the meaning of that? So I can check the right DAX cose of that

@Deku suggested

Active = calculate( countrows( facts), fact[status] = "Active" )

Inactive = calculate( countrows( facts), fact[status] = "Inactive" )

Sales = sum( fact[sales] )

Rest will be

Total Sales Q1 = CALCULATE ( [Sales], Calendar[Quarter]="Q1" )

Total Sales Q2 = CALCULATE ( [Sales], Calendar[Quarter]="Q2" )

Ratio = DIVIDE ( [Total Sales Q1], [Total Sales Q2] )

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

but I want to be sure that Active is a count of rows or might be a number of distinc values of some column, can you clarify this?

Thanks

[E5254730]

@FBergamaschi 

Active, means the Projects which are Active or Inactive.

As you said, Active is the count of rows and not distinc values

Do I need to create variables to achieve the table output as posted in my initial post or just Dax? 

[E5254730]

@FBergamaschi @Deku 

I was able to achieve the expected results with the sample data that I shared.

But when I added my actual data, it gives the count of all the years quarter wise.

In the actual data there are previous years data.

And when I filter Relative Date is in this year, the count is returning zero.

Any idea what is causing this error.

[FBergamaschi]

Please share an image of the error, with details of the visual settings 

Thx

[Deku · Accepted solution]

Add a extra condition in the calculate(). If the calendar table extends past the current year could use year( today() ) or some other logic if you want filtering to affect it

Like

Total Sales Q1 =

Var maxYear = calculate(max( calendar[year] ), removefilters( calender) )

Return

CALCULATE ( [Sales], Calendar[Quarter]="Q1"

Calendar[year] = maxYear)

[Deku · Accepted solution]

Add a date table, joined to the reporting date column, with a quarter column

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Add classification to rows and quarter to columns for a matrix visual. Then you would need measures for the values i.e.

Active = calculate( countrows( facts), fact[status] = "Active" )

Inactive = calculate( countrows( facts), fact[status] = "Inactive" )

Sales = sum( fact[sales] )

Not sure how you want to calculate the card percentages or the green Vs grey sales

[E5254730]

@Deku @FBergamaschi 

Thanks! Meanwhile I try with the logics provided by you, you can reference below sample table of Q2

Project Type	Classification	Reporting Date	Sales	Status
Strategic	Chronological	Apr-25		Inactive
Strategic	Chronological	Apr-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	Apr-25	72419.68	Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	Apr-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25	159000.00	Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	Jun-25	275000.00	Inactive
Strategic	Chronological	Apr-25	670000.00	Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	Jun-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	Apr-25		Inactive
Strategic	Chronological	Apr-25	163021.00	Inactive
Strategic	Chronological	May-25	140000.00	Inactive
Strategic	Chronological	May-25		Inactive
Strategic	Chronological	Apr-25		Inactive
Strategic	Chronological	Apr-25		Inactive
Strategic	Chronological	Apr-25	20535576.00	Active
Strategic	Chronological	Apr-25		Inactive

[FBergamaschi]

It is certainly possible using a matrix with classification in rows and quarters in columns and then the DAX measures in Values

You should first create a calendar table with CALENDARAUTO () and connect it to the Sales table

For the card, I am confused by the meaning on the cells, for example one labe says Total Sales / Total Sales with value 56%?!

If you want more help

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here