Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

[SOLVED] Getting correct sums from unpivoted table in star schema

Hi everyone, I'm having some difficulty with a new dashboard I'm working on. This is my first time implemenenting a star schema but I have one central "fact" table surrounded by "dimension" tables, linked with unique keys. I have several columns of survey data in the fact table that need to be unpivoted but this is causing duplicates of all the other value fields in the fact table. Here is a mock up of my starting data:

 

IDValueSurvey1Survey2
1101.12.1
1101.112.11
2201.22.2

 

And here it is after the unpivot:

 

IDValueSurveyAnswer
110Survey11.1
110Survey22.1
110Survey11.11
110Survey22.11
220Survey11.2
220Survey22.2

 

As you can see, after the survey columns are unpivoted, there are duplicate values in the value column. So, for example, if I want a card that shows the sum of an ID's value column, its much bigger than it should be. How do I work around this without creating another table and ruining the star schema? Happy to provide more context, thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi everyone, thanks for the suggestions! I actually found a solution from another post that does exactly what I need. Here is the formula I used in case anyone else has the same question:

 

Calculated Value = SUMX(VALUES(Grouping ID),CALCULATE(MAX(Value)))

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi everyone, thanks for the suggestions! I actually found a solution from another post that does exactly what I need. Here is the formula I used in case anyone else has the same question:

 

Calculated Value = SUMX(VALUES(Grouping ID),CALCULATE(MAX(Value)))

 

 

HotChilli
Super User
Super User

There's nothing wrong with the design.  When you know that values should only be summarized in specific ways (for example a SUM here makes no sense), you take that into account when creating the visualisations (by using the appropriate default summarization).

You can control how other users use the field by creating a measure (in this case it would be MIN(Value))

Anonymous
Not applicable

Okay well at least my data model is fine, still learning and all that...

 

However I'm not sure MIN(Value) is correct here. In my mock data, ID = 1 has two rows, so the correct sum would be 20, but if I used MIN(Value) I would get 10. I know there are probably quite a few ways to do this but what would you recommend? Also its worth noting that the real datasets are much more complicated and have multiple levels of unpivoting and aggregation. Thanks for the answer!

l get back to you (i'm in the middle of something right now).

You're quite right about MIN not being correct (I didn't really look at your data closely)

I wouldn't say there is a definitive answer . Whatever gets the job done in a reasonably efficient, performant manner is correct (taking account of good design practice as much as possible)

A few solutions:

1. A measure would be 

 DIVIDE(SUM(TableP[Value]), DISTINCTCOUNT(TableP[Survey]))

I think that produces the 20, 20 result to show against ID.

 

or 2. You could create a dimension to hold the ID and Total Value .  This would be created in Power Query from the original 'pivoted' table with a  GROUP BY 'id' and SUM the Value.

When creating a visual, if it's simply ID and Value just take the values from this dimension.

 

or 3. You could divide up the Value when unpivotting the table (assign a value to each line in the fact table to represent the share - so ID 2 would have 2 lines of value 10.  That would make the aggregation easier , I think it would just be SUM(Value). I've no idea if this makes sense in the specific business scenario i.e. does ID 2 still have a value of 20 if there is only one survey?

 

 

Hope that helps. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.