Forum Discussion

E5254730's avatar
E5254730
Icon for Helper II rankHelper II
1 year ago
Solved

Quarter Wise Calculations in a Table format

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

 

Need Help
  • Deku's avatar
    Deku
    1 year ago

    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's avatar
    FBergamaschi
    1 year ago

    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

  • Deku's avatar
    Deku
    1 year ago

    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)

     

     

12 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

    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

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      E5254730
      Icon for Helper II rankHelper II
      1 year ago

      Deku FBergamaschi 

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

      Project TypeClassificationReporting DateSalesStatus
      StrategicChronologicalApr-25 Inactive
      StrategicChronologicalApr-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalApr-2572419.68Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalApr-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25159000.00Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalJun-25275000.00Inactive
      StrategicChronologicalApr-25670000.00Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalJun-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalApr-25 Inactive
      StrategicChronologicalApr-25163021.00Inactive
      StrategicChronologicalMay-25140000.00Inactive
      StrategicChronologicalMay-25 Inactive
      StrategicChronologicalApr-25 Inactive
      StrategicChronologicalApr-25 Inactive
      StrategicChronologicalApr-2520535576.00Active
      StrategicChronologicalApr-25 Inactive
    • FBergamaschi's avatar
      FBergamaschi
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
        E5254730
        Icon for Helper II rankHelper II
        1 year ago

        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? 

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    1 year ago

    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