Forum Discussion

Byzza's avatar
Byzza
Frequent Visitor
1 year ago
Solved

DAX & Table Query Best Practice

Hi Brains Trust, I've recently been setting up our Data Warehouse in MS Fabric and in the process of setting up some Semantic models for the team to use which they can create their reports from.   ...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Byzza - You're on the right track with the idea of merging tables and unpivoting. Here’s a best-practice design to simplify modeling and comparison across all sources.

    In Power Query:
    Import the wide table
    Use “Unpivot Columns” to transform all value columns (like Volume, Revenue…) into rows under Item + Value
    Add columns: Source, Phasing, and any necessary metadata.

    You can write one dynamic DAX measure that works across all items (using SELECTEDVALUE or slicers)

    Actual vs Budget Variance %

    Variance % =
    VAR Actual =
    CALCULATE([Total Value], FactTable[Source] = "Actuals")
    VAR Budget =
    CALCULATE([Total Value], FactTable[Source] = "Budget")
    RETURN
    DIVIDE(Actual - Budget, Budget)

     

     

     

     

    Hope the above suggestion and details helps.

     

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi Byzza - You're on the right track with the idea of merging tables and unpivoting. Here’s a best-practice design to simplify modeling and comparison across all sources.

In Power Query:
Import the wide table
Use “Unpivot Columns” to transform all value columns (like Volume, Revenue…) into rows under Item + Value
Add columns: Source, Phasing, and any necessary metadata.

You can write one dynamic DAX measure that works across all items (using SELECTEDVALUE or slicers)

Actual vs Budget Variance %

Variance % =
VAR Actual =
CALCULATE([Total Value], FactTable[Source] = "Actuals")
VAR Budget =
CALCULATE([Total Value], FactTable[Source] = "Budget")
RETURN
DIVIDE(Actual - Budget, Budget)

 

 

 

 

Hope the above suggestion and details helps.