Forum Discussion
Cross tab format/formula help needed
Is there a way to achieve this format in one table? I can do NS (net sales) and NM% (net margin %) metrics as row labels with a rolling 7 months at the top in one table and the Year to date metrics in another, but I can not figure out how to have it all in one table. Below is a screen shot of what I need it to ultimately look like. I can not for some reason figure out how to attach the excel example...
The first column is Distribution Center, the second is Ship Method, the third is Plan Group and the 4th are the metrics for Net Sales and Net Margin %. After the months it is "Current Year Year to Date", "Prior Year Year to Date", "Year to Date Variance", "Budget Year to Date" and "Budget Year to Date Variance".
Hi Ifholland
Consider this solution ...
You need to create a "driver" table.
Use Power Query to create a dynamic query that rolls on the dates for each refesh
and create a driver table like this ....
ID Name 1 20-Nov 2 21-Jun 3 21-Jul 4 21-Aug 5 21-Sep 6 21-Oct 7 21-Nov 8 CY YTD 9 PY YTD 10 YTD Var 11 BUD YTD 12 YTD Var
Create measure for each columns,Then create a dynmaic DAX formula ....
Answer =
VAR myID = SELECTVALUE(Driver(ID))
RETURN
SWITCH(myID,1, [measure1],
2, [measure2],
3,[measure3],
etc)
Draw matric visual with column = Name and Value = Answer and Row = as per your diagram
I am a Power BI volunteer. Please click thumbs up if you like me trying to help you. Also click solved if this fixes your problem. One problem per ticket please. If you need to expand or change your problem then please set this one to solved and raise a new ticket.
5 Replies
Hi Ifholland
Consider this solution ...
You need to create a "driver" table.
Use Power Query to create a dynamic query that rolls on the dates for each refesh
and create a driver table like this ....
ID Name 1 20-Nov 2 21-Jun 3 21-Jul 4 21-Aug 5 21-Sep 6 21-Oct 7 21-Nov 8 CY YTD 9 PY YTD 10 YTD Var 11 BUD YTD 12 YTD Var
Create measure for each columns,Then create a dynmaic DAX formula ....
Answer =
VAR myID = SELECTVALUE(Driver(ID))
RETURN
SWITCH(myID,1, [measure1],
2, [measure2],
3,[measure3],
etc)
Draw matric visual with column = Name and Value = Answer and Row = as per your diagram
I am a Power BI volunteer. Please click thumbs up if you like me trying to help you. Also click solved if this fixes your problem. One problem per ticket please. If you need to expand or change your problem then please set this one to solved and raise a new ticket.
I was thinking something along these lines. This may help me get over that hump! I'll let you know how it works out!
I helped you, now please can you help me with kudos.
I am a unpaid Power BI volunteer.
Please click the thumbs up and Accept as Solution for taking time to help you.
Thank you 😁I haven't quite figured out how to implement it yet and I have EOM stuff to finish up before I can tackle it again. One challenge, I've never used Power Query, so I need to figure a work around that. Second, the months are rolling, so I need to figure out how to create dynamic names for the individual months.
Hi IFHolland.
I helped you, so please help me with kudos.
I am a unpaid Power BI volunteer.Please accept solutions and then raise one ticket per new problem, rather than wait until EOM and tag more problems to the same ticket.
You will get a better reponse that way from everyone, and volunteers get the kudos they deserve.
Thank you 😁
