Forum Discussion

ferryberry's avatar
ferryberry
Frequent Visitor
2 years ago
Solved

Visual breaks with fields from two DIM tables

Dears,   hope you can help me here, I'm getting nuts. Below you can see my data model. It has two FACT tables, which are related to two DIM tables.   1. Relationships - DIM Geo: one-to-many. To...
Need Help
  • ferryberry's avatar
    ferryberry
    2 years ago

    Hi Anonymous ,

    I found a solution, and it has nothing to do with relationships. I keep my model as described.

    I've implemented a SUM as amitchandak suggested in this post:

     
    Total Turnover = CALCULATE(SUM('02b. FACT_Turnover'[Turnover]),FILTER('02b. FACT_Turnover','02b. FACT_Turnover'[Material] in VALUES('02a. FACT_Materials'[Material])))
     
    amitchandak , now I'd like to do a running total (or cummulative sum). I've successfully added a Rank column, and I'd like to do this running total using this column. Following my data mode described in this post, what do you suggest? I've tried this formula, but no success:
    Running Total =
    VAR currank2 = [Rank99]
    RETURN
        CALCULATE (
            SUMX('02b. FACT_Turnover',[Turnover]),
            FILTER(ALLSELECTED('02a. FACT_Materials'), [Rank99] <= currank2)
    )
     
    Thanks,
Anonymous's avatar
Anonymous
Not applicable
2 years ago

ferryberry  That looks like a relationship issue. I'd have to see what you're joining on. You might also need to modify the cross filter direction.

ferryberry's avatar
ferryberry
Frequent Visitor
2 years ago

Hi Anonymous ,

 

thanks for your quick reply. Relationships:

 

1. From DIM GEO to FACT Materials:

 

 

 

 

 

 

 

2. From DIM GEO to FACT Turnover:

 

 

 

 

 

 

3. From DIM Materials to FACT Materials:

 

 

 

 

 

 

 

4. From DIM Materials to FACT turnover:

 

 

 

 

 

 

Moreover, look at the visual below.

I have fields from both DIM tables, as well as both FACT tables (Actual Safety Days from DIM Materials, and Sum of Turnover, from FACT Turnover). In this case, "Sum of Turnover" is not a measure, but the same field "Turnover", which Power BI automatically summarizes by adding up.

Problem is I can't replicate this by a measure. Say SUM('FACTtable[Turnover]) breaks everything.

 

 

 

 

 

 

 

 

 

 

 

 

Any suggestion?

Thanks,