cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clarkpaul
Helper I
Helper I

Create a Summary Table from unique data

This should seem simple but I can't wrap my head around it.

I have a table with item quantities compared to the 6 Month Usage. The Qty is either less that the 6 month usage or greater than it.

It also includes the associated extended cost.
There are times where there is both a qty less than the 6 Month Usage AND more than the 6 Month Usage.

I'm trying to produce a summary table like the one below the detail data below.

It has 2 categories, On Hand > 6 months and On Hand < 6 months with totals for Quantity and Cost.

Any help would be greatly appreciated!

clarkpaul_0-1608728009573.png

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @clarkpaul ,

 

I would do the folllowing:

 

  • Create a table for the category:

Category                        ID

On Hand > 6 Month 1
On Hand <= 6 Month

2

 

  • Add the following two measures:
Cost = SWITCH( 
            SELECTEDVALUE(Category[ID]);
            1 ; SUM('Table'[On Hand > 6 Months Usage Cost]); 
            2 ; SUM('Table'[On Hand <= 6 Month Usage Cost]);
            SUM('Table'[On Hand <= 6 Month Usage Cost]) + SUM('Table'[On Hand > 6 Months Usage Cost])
        )

Quantity = SWITCH(
                SELECTEDVALUE(Category[ID]); 
                1 ; SUM('Table'[On Hand > 6 Month Qty]); 
                2 ; SUM('Table'[On Hand <= 6 Mnoth Qty]);
                SUM('Table'[Qty])
            )

 

See result in attach PBIX file .

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @clarkpaul ,

 

I would do the folllowing:

 

  • Create a table for the category:

Category                        ID

On Hand > 6 Month 1
On Hand <= 6 Month

2

 

  • Add the following two measures:
Cost = SWITCH( 
            SELECTEDVALUE(Category[ID]);
            1 ; SUM('Table'[On Hand > 6 Months Usage Cost]); 
            2 ; SUM('Table'[On Hand <= 6 Month Usage Cost]);
            SUM('Table'[On Hand <= 6 Month Usage Cost]) + SUM('Table'[On Hand > 6 Months Usage Cost])
        )

Quantity = SWITCH(
                SELECTEDVALUE(Category[ID]); 
                1 ; SUM('Table'[On Hand > 6 Month Qty]); 
                2 ; SUM('Table'[On Hand <= 6 Mnoth Qty]);
                SUM('Table'[Qty])
            )

 

See result in attach PBIX file .

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



amitchandak
Super User
Super User

@clarkpaul , Summarize can help.

 

example, the way two get 2nd from first 

union(
summarize(Table, "Category", "On Hand > 6 months", "Quantity",[On Hand Qty > 6 Month], "Cost",[On Hand Qty > 6 usage Cost]),
summarize(Table, "Category", "On Hand <= 6 months", "Quantity",[On Hand Qty <= 6 Month], "Cost",[On Hand Qty <= 6 usage Cost])
)

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors