Forum Discussion

pankajgurav's avatar
pankajgurav
Regular Visitor
8 months ago
Solved

Dyanamic Dashboard based on Excel Selection

Hi

I have an Excel file of an ABCS company. It contains two tables. The Column Particulars and the amount column have values, and Columns A, B, C, and D are formula-based columns. This data is only for one quarter. Similarly, there is data for three quarters. I also have Excel files of multiple companies in the same format.

I want a dynamic dashboard in which I can select two companies and select a period. In the Power BI dashboard, I should be able to check/compare the difference between the two selected companies.

 

Thanks,

Pankaj

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    8 months ago

    This is a modeling problem, not a dashboard trick. Once the data is shaped correctly, the dashboard part is easy.

    What to do (high level):

     

    1. Stop relying on Excel formulas
      Power BI doesn’t work well with formula-based columns across many files.
      Bring in only:

    • Company

    • Quarter / Period

    • Particular

    • Amount

    Recreate any logic (A/B/C/D) in Power BI (Power Query or DAX).

     

    1. Combine all companies + quarters into one fact table
      In Power Query:

    • Load all Excel files (Folder connector)

    • Add:

      • Company

      • Quarter / Period

    • Append everything into one table

    Final grain example:

    Company | Period | Particular | Amount

     

    1. Create dimensions

    • DimCompany

    • DimPeriod (Quarter)

    • (Optional) DimParticular

  • Kedar_Pande's avatar
    Kedar_Pande
    8 months ago

    pankajgurav 

    - Extract Company/Quarter from filename/sheet
    - Unpivot A,B,C,D columns
    - Result: Company | Quarter | Particulars | Amount | Category

     

    Company 1 = CALCULATE(SUM(Fact[Amount]), Company[Name] = SELECTEDVALUE(Company[Name]))
    Company 2 = 
    VAR Co1 = SELECTEDVALUE(Company[Name])
    VAR Co2 = CALCULATETABLE(VALUES(Company[Name]), Company[Name] <> Co1)
    RETURN CALCULATE(SUM(Fact[Amount]), Company[Name] IN Co2)

    Difference = [Company 1] - [Company 2]

     

     

5 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    8 months ago

    This is a modeling problem, not a dashboard trick. Once the data is shaped correctly, the dashboard part is easy.

    What to do (high level):

     

    1. Stop relying on Excel formulas
      Power BI doesn’t work well with formula-based columns across many files.
      Bring in only:

    • Company

    • Quarter / Period

    • Particular

    • Amount

    Recreate any logic (A/B/C/D) in Power BI (Power Query or DAX).

     

    1. Combine all companies + quarters into one fact table
      In Power Query:

    • Load all Excel files (Folder connector)

    • Add:

      • Company

      • Quarter / Period

    • Append everything into one table

    Final grain example:

    Company | Period | Particular | Amount

     

    1. Create dimensions

    • DimCompany

    • DimPeriod (Quarter)

    • (Optional) DimParticular

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    8 months ago

    pankajgurav 

    - Extract Company/Quarter from filename/sheet
    - Unpivot A,B,C,D columns
    - Result: Company | Quarter | Particulars | Amount | Category

     

    Company 1 = CALCULATE(SUM(Fact[Amount]), Company[Name] = SELECTEDVALUE(Company[Name]))
    Company 2 = 
    VAR Co1 = SELECTEDVALUE(Company[Name])
    VAR Co2 = CALCULATETABLE(VALUES(Company[Name]), Company[Name] <> Co1)
    RETURN CALCULATE(SUM(Fact[Amount]), Company[Name] IN Co2)

    Difference = [Company 1] - [Company 2]

     

     

  • danextian's avatar
    danextian
    Super User
    8 months ago

    You will get varying answers because there is no sample data to begin with. We dont even know visually how your data source looks like. If you want a solution that is as closes to your use case, Please provide a workable sample data (not an image and with confidential data removed), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.  Please refer to this link https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

     

  • v-prasare's avatar
    v-prasare
    Community Support
    7 months ago

    Hi pankajgurav.,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

    danextian, Kedar_Pande& cengizhanarslan, thanks for your prompt response

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    7 months ago

    Hi @pankajgurav.,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support