Forum Discussion
Helper IIQuarter Wise Calculations in a Table format
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
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
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)
Helper IITotal Sales/Total Sales = Total Sales of Q1 / Total Sales of Q2
Super UserOK 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
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
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.
- Deku
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)