Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

aggregate fact table sales based on a dim table column that are related through another dimension table

  • 1.  aggregate fact table sales based on a dim table column that are related through another dimension table

    Posted 10 days ago

    Hello There,

    I have a situation, I want to get the reseller sales for "WASHINGTON REGION"; the database is "AdventureWorksDW2019" and I have the column relation as follows:

     

    The geography details are populated in "DimGeography" table,

     

    The reseller details are populated in "DimReseller" table, where each geography, say in my case, WASHINGTON could have more then reseller; hence, there is many-to-one relation between "DimReseller" and "DimGeography" and the column in relation is "GeographyKey"

    The fact table "FactResellerSales" table is populated with reseller-sales; hence there is many-to-one relation between "FactResellerSales" and "DimReseller" tables; the column in relation is "ResellerKey"

        --and the fact table has indirectly relation with "DimGeography" table

     

    I am struggling generate a DAX query to get reseller-sales total belonging to "Washington"; could anyone help me understand to achieve this?

    Thank you for giving your valuable time; pls find the screenshot of the schema diagram attached to this email.



    ------------------------------
    ArunaRamana Program
    student
    ------------------------------


  • 2.  RE: aggregate fact table sales based on a dim table column that are related through another dimension table

    Posted 9 days ago
    I'm a novice at DAX queries, but something I'm noticing is that the location "Washington" can be problematic in geographic applications in dashboarding. It could be that the Washington Region data type in your tables is nominal or categorical and is simply a label. However, if it is tied to geography, it might help double-checking the data type in the table view, right-clicking on the column, and telling Power BI that Washington is or is not a State.

    ------------------------------
    Ben Hoganson
    ------------------------------