Dyanamic Dashboard based on Excel Selection

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

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]