98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Power BI
Power BI
Resolved · Low Priority · Version 2016
Wed 20 Mar 2019, 16:59Delegate Sarah said...
Sarah has attended:
Excel VBA Introduction course
Power BI
Hi Jens,
How do I group by product and by geography?
thank you!
S
Thu 21 Mar 2019, 09:41Trainer Jens said...
RE: Power BI
Hi Sarah,
Thank you for the forum question and thank you for the nice feedback.
Please copy the dax below and paste it in a new measure. You will need to amend it a little bit. I do not know the name of your table or your headers. In my code the table name is Sheet1 and have a header named Product. In my column product I have Alkaline, Duracell all the different batteries descriptions you want to have the subtotals from per geography.
Amend the dax - add the columns to a matrix (Geography, product, sales, and the measure). Drill the matrix one step down (the double connected down arrows) and should have what you need.
I hope this makes sense.
Measure % Unique =
VAR this_Unique =
SUM ( Sheet1[Sales] )
VAR group_unique =
CALCULATE (
SUM ( Sheet1[Sales] ),
ALL ( Sheet1[Product] )
)
RETURN
DIVIDE ( this_unique, group_unique )
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 21 Mar 2019, 14:06Delegate Sarah said...
RE: Power BI
Hi Jens!
Thanks for a great couple of days and for a swift reply on this one.
I think it is almost there.... with one minor tweak needed.
I have copied and amended your DAX in.... but the challenge is that within "products" there is a "total batteries", which is what it will need to be divided by for that customer.
The headings I have with what is included are:
Geography: All Geographies, Asda, Tesco, JS, Morrisons, Wilko, Waitrose.
Product: Total Batteries, Duracell, Energizer, Panasonic, Private Label.
So, a couple of example of what i'm looking for are:
1. Tesco Duracell / Tesco Total Batteries
2. All Geographies Duracell / All GeographiesTotal Batteries
For reference, the below is your DAX with my report naming......
Measure % Unique = VAR this_Unique =SUM ( '2019 Share Report - weekly tra'[Value Sales] )VAR group_unique =CALCULATE (SUM ( '2019 Share Report - weekly tra'[Value Sales] ),ALL ( '2019 Share Report - weekly tra'[Product] ))RETURN DIVIDE ( this_unique, group_unique )
Thanks again for your help on this!
Sarah
Thu 21 Mar 2019, 15:06Trainer Jens said...
RE: Power BI
Hi Sarah,
You should be able to get what you want with the dax I sent you, or may be it is not clear to me what you want.
What I think is the issue is how your source data are arranged. We shouldn't have totals or subtotals in the source data. You can clean out your totals and subtotals in Power Query by the filter next to the headings.
I have attached an Excel file and a Power Bi file.
If you look at the Excel file I have "cleaned" the source date from subtotals and totals.
In the Power Bi file you can see the report and I think it displays what you want.
Be a little bit patient. If you cannot see the attachment under my signature wait 5 minutes and refresh your browser.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
duracel2.xlsxThu 21 Mar 2019, 15:33Delegate Sarah said...
RE: Power BI
Hi Jens,
again thank you for replying so quickly.
The data in my file is clean of sub totals - but "Total Batteries" and "Total Geograpahies" is not always just a sum of "panasonic", "Duracell", "Energizer" and "Private Label" - which is why they are there....
does that make sense?
Thanks
S
Thu 21 Mar 2019, 16:19Trainer Jens said...
RE: Power BI
Hi Sarah,
"Total Batteries" is "others" plus "panasonic", "Duracell", "Energizer" and "Private Label".
And
"Total Geographies" is the sum of everything; "others" plus "panasonic", "Duracell", "Energizer" and "Private Label" for all Geographies.
If you in the source change "Total Batteries" to "Others" and in the source calculate "Total batteries" minus "panasonic", "Duracell", "Energizer" and "Private Label".
This will change youR source data to a Power Bi friendly list and you can use the dax I have provided.
This is an important thing to do. The visuals in Power Bi are created to sum up the data. If you have "Total Batteries" in your source the visuals will add "Total Batteries" to the rest of the products.
Right now I think it is the only solution, but if I get a better idea, I will be back.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Editing a formula quicklyIf you want to edit a fomrula or text quickly witin a cell instead of the formula bar, you can click either double click in the cell or press the F2 key |
